Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

How to pick up last week's data of that particular month in QlikView?

Hello Everyone,

I need help on the below requirement.

I have a date field which has dates of only Fridays of that particular month. So this field will have 4 or 5 date values for a month based on number of Fridays in that month. What I want to achieve is, there are AOP and Actual figures against these dates, and in my straight table I would like to display only the data of last week in that particular month. And for the current month, data should be the latest week data.

For Example: Considering that below is the table in excel sheet, I want to display only data for AOP and Actuals for Date, 25-08-2017 for month of August and for September, I have to show data for Date, 01-09-2017 as this is the latest week in September so far.

DateAOPActuals
04-08-2017755585
11-08-201738954
18-08-2017876766
25-08-201725686
01-09-2017784233
08-09-2017265-
15-09-2017987-

How can I show this data in a Straight table? Any suggestions?

Thank you.

15 Replies
apoorvasd
Creator II
Creator II
Author

Null value.

If it is possible to see previous value, then please let me know how?

Thank you.

tresesco
MVP
MVP

PFA

Capture.PNG

apoorvasd
Creator II
Creator II
Author

Hi Tresesco,

Great! That was what I wanted. But with one small change, when I do not make any selection in year or month fields, it displays only the latest week value. Is it possible to display last value of each month when nothing is selected?

Also please let me know what were the changes that you made? Did you make any change in the script side or just in the expression?

Thanks a lot for your help!

tresesco
MVP
MVP

Just add second dimension - POPER_NEW

Capture.PNG

apoorvasd
Creator II
Creator II
Author

Hi Tresesco,

How can I show null values for ACT? I tried changing the expression by removing the highlighted line as shown here : FirstSortedValue(DISTINCT {<[ACT Inventory weekly LC]={'>0'},Date={'<=$(=Today())'}>}[ACT Inventory weekly LC],-Date), but it doesn't work (I see null values for all the dates if I remove that line). What else can I do to see null values?

Thank you.

apoorvasd
Creator II
Creator II
Author

Yes, that worked!

Any idea on the NULLS?

Thank you very much!