topic Re: Previous Day Set Analysis...?? in QlikView

Previous Day Set Analysis...??

Sun, 26 Jul 2015 13:15:31 GMT

Hello,

I am working on to display previous day results in the text box, when user selects for a particular day... I have date field (FECDATA) and created list boxes based on the following expressions

Listbox:==Expressions used

Year == Year(FECDATA)

Month == Month(FECDATA)

Days === Day(FECDATA)

Text Box Expressions:

=SUM({<COD2={'1007t0000'},IDES={'01'},FECDATA={'$(=max(FECDATA)-1)'},FECDATA=>} Sales) Returns Total Sales

=SUM({<COD2={'10070000'},IDES={'01'},FECDATA={'$(=max(FECDATA)-1)'} >} Sales) Returns Zero

i have slightly modified the load statement to create another field for dates based on the original FECDATA as FECDATA10

LOAD

FECDATA,

FECDATA AS FECDATA10

....

i have slightly modified the statement as to avoid the user selections(for find the previous day results) based on FECDATA :

=SUM({<COD2={'1007t0000'},IDES={'01'},FECDATA10={'$(=max(FECDATA)-1)'},FECDATA=>} Sales) Returns Zero Sales instead of previous day...

=SUM({<COD2={'1007t0000'},IDES={'01'},FECDATA={'$(=max(FECDATA10)-1)'},FECDATA=>} Sales) returns Total Sales

=SUM({<COD2={'1007t0000'},IDES={'01'},FECDATA10={'$(=max(FECDATA10)-1)'},FECDATA=>} Sales) returns Zero Results

How to display the previous day results using set analysis???

Thans a lot in advance for all your help or consideration.

Regards

Siva

Re: Previous Day Set Analysis...??

marcus_sommer — Sun, 26 Jul 2015 14:27:23 GMT

I would try it in this way:

FECDATA={"$(=date(max(FECDATA)-1), 'FormatFromFECDATA')"}

then max(date) will return a numeric value like 42111 instead of '07/26/YYYY' or what formating you have. Further you need to make sure that max(FECDATA) isn't in the future because you might have budget- or calendar-entries which going further then today() and have of course no sales.

- Marcus

Re: Previous Day Set Analysis...??

Sun, 26 Jul 2015 15:31:42 GMT

Marcus,

thanks for your reply....

i am still gettting the zero value for these two expressions.......

SUM

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA))-1,'DD/MM/YYYY')"}>} sales)

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA))-1,'DD/MM/YYYY')"},FECDATA=>} sales)

Did i miss anything..? i dont have any future dates in the data model....

Regards

Siva

Re: Previous Day Set Analysis...??

marcus_sommer — Sun, 26 Jul 2015 15:51:19 GMT

Within my suggestion was a error with the parenthesis then the date() ended here already behind the max(), therefore now:

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA)-1,'DD/MM/YYYY'))"}>} sales)

- Marcus

Re: Previous Day Set Analysis...??

Kushal_Chawda — Sun, 26 Jul 2015 16:36:37 GMT

Try this

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA)-1,'DD/MM/YYYY'))"}>} sales)

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA)-1,'DD/MM/YYYY'))"},FECDATA=>} sales)

Re: Previous Day Set Analysis...??

Sun, 26 Jul 2015 18:29:02 GMT

Excellent......It's working now..... Thanks a lot for all your help...

Correct expression is :

sum({<COD2={'10070000'},IDES={'01'},FECDATA={"$(=date(max(FECDATA)-1,'DD/MM/YYYY'))"}>} sales)

As Correct Answer option is not appering for me to vote you...am making it as helpful answer as of now...

Thanks a lot again to both of you...

Regards

Siva

Re: Previous Day Set Analysis...??

qlikviewwizard — Mon, 27 Jul 2015 01:28:49 GMT

Hi sivadandu

Please close the thread by selecting Correct Answer.