Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

shumailh
Contributor III

Set Analysis Max Date

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

Tags (1)
5 Replies
Not applicable

Set Analysis Max Date

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.

shumailh
Contributor III

Set Analysis Max Date

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

Not applicable

Set Analysis Max Date

FirstSortedValue() might work for you here.

Not applicable

Set Analysis Max Date

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)


Not applicable

Set Analysis Max Date

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)

Community Browser