Set analysis return 0

[Anonymous]

Hi Community,

Here is my expression : Sum({<SaleDate = {'Max(SaleDate)-1'}>} Amount)

from this I got 0 value, while I expect more than 0. please help.

[narender123]

Try this

=Sum({<SaleDate = {'$(=Max(SaleDate)-1)'}>} Amount)

If this is not working then tell me the formate of sale date

Thanks.

[Sokkorn · Accepted solution]

Hi,

This should work:

Sum({<SaleDate = {$(=Date(Max(SaleDate)-1,'dd-MM-yyyy'))}>} Amount)

Assum that SaleDate have format dd-MM-yyyy

and if you have other format beside above mention, then change format in expression according to your SaleDate.

Regards,

Sokkorn

[Anonymous]

Thank you Sokkorn. I got some amount. Need to check for correct number.

[Anonymous]

Sokkorn, your method working like a charm. The total amount correct also. Thank you all for your help.

[Anonymous]

Sokkorn, one more question. Why we need to use Date(Max(SaleDate)-1,'dd-MM-yyyy'))? I try Sum({<SaleDate = {"$(=Max(SaleDate)-1)"}>} Amount) like er.mohit's method, but get 0 value. This one should work. Why it give me wrong result?

-H

[Sokkorn]

Hi,

Like this:

You have SaleDate in date format; dd-MMM-yyyy. Ex. 18-Jul-2013

Then Max(SaleDate)-1 will return in number format. Ex Max('18-Jul-2013')-1 return 41472

So expression will return 0 while it compare 18-Jul-2013 to 41472; mean that compare Date with Number.

That why we need to format Max(SaleDate)-1 into date format.

Hope it clear.

Regards,

Sokkorn