Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis return 0

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.

1 Solution

Accepted Solutions
Sokkorn
Master
Master

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

View solution in original post

6 Replies
narender123
Specialist
Specialist

Try this

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

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

Thanks.

Sokkorn
Master
Master

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Master
Master

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