topic Re: Set analysis return 0 in QlikView

Set analysis return 0

Anonymous — Thu, 18 Jul 2013 06:31:39 GMT

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.

Re: Set analysis return 0

narender123 — Thu, 18 Jul 2013 06:33:51 GMT

Try this

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

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

Thanks.

Re: Set analysis return 0

Sokkorn — Thu, 18 Jul 2013 07:04:59 GMT

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

Re: Set analysis return 0

Anonymous — Thu, 18 Jul 2013 07:07:31 GMT

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

Re: Set analysis return 0

Anonymous — Thu, 18 Jul 2013 07:10:35 GMT

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

Re: Set analysis return 0

Anonymous — Thu, 18 Jul 2013 07:22:00 GMT

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

Re: Set analysis return 0

Sokkorn — Thu, 18 Jul 2013 07:30:20 GMT

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