Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Previous Day Set Analysis...??

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

View solution in original post

6 Replies
MVP & Luminary
MVP & Luminary

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

Not applicable

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

MVP & Luminary
MVP & Luminary

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

View solution in original post

MVP
MVP

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)

Not applicable


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

Master II
Master II

Hi sivadandu

Please close the thread by selecting Correct Answer.