topic Set Analysis Max Date in QlikView

Set Analysis Max Date

shumailh — Fri, 03 Sep 2010 20:47:58 GMT

Can we get the amount as per max invoice date if the user selection of invoice date varies
for eg if i select specific range for invoice date i.e.31-jul-2010 to 31 jan 2010 then is that possible in set analysis that i can get the amount for 31-jul-2010. whereas if we remove the selection we have 31-aug-2010 as a max invoice date.

Currently i am doing it mannually which is not giving me the required result by changing the selection. i.e max invoice date



Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'}, Invoice_Date = {'31/07/10'}>} Sales_Amount)

Shumail

Set Analysis Max Date

Fri, 03 Sep 2010 21:02:57 GMT

Something like:

Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
 Invoice_Date = {'$(=Max(Invoice_Date))'}>} Sales_Amount)

Is Aug 31st the max date in your data set? If nothing is selected, then this will return for the max date in your data set. If there is a date selection, it will be the max date in that selection.

Set Analysis Max Date

shumailh — Sat, 04 Sep 2010 11:52:45 GMT

Miller,

I tried the same code as above but it is not working. I want to tell u one more thing that the invoice_date is in numeric format i.e. 40421 = 31-aug-2010, 40390 = 31-jul-2010.. I am not understanding why the max function is not working here. Any Idea???

Shumail

Set Analysis Max Date

Sun, 05 Sep 2010 00:27:14 GMT

FirstSortedValue() might work for you here.

Set Analysis Max Date

Tue, 07 Sep 2010 19:22:23 GMT

Try setting up a straight table chart with your expression, but don't give it a label. Then when the chart is rendered, your label will be the expression with the dollar sign expansion evaluated. This will tell you if your dollar sign expansion is returning a value that matches up with what your invoice date field needs (i.e. a numeric date).

Also, you could try hardcoding a date value into your set expression:

Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
 Invoice_Date = {40390}>} Sales_Amount)

Either of these actions will tell you if it is your Set Analysis or your dollar sign expansion that is the problem.

You should also try getting rid of the quotes inside the set modifier of the expression I gave. Since your values are numeric, you don't need the quotes and that could be the problem:

Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
 Invoice_Date = {$(=Max(Invoice_Date))}>} Sales_Amount)

Set Analysis Max Date

Tue, 07 Sep 2010 19:28:59 GMT

Hi there, you can try adding the function date to the number returned by the max expression, like this:

Regards

Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
Invoice_Date = {'$(=date(Max(Invoice_Date)))'}>} Sales_Amount)