Set Analysis Max Date

[shumailh]

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

[#2]

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]

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

[#4]

FirstSortedValue() might work for you here.

[#5]

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)

[#6]

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)