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

Only display last 6 weeks

HI,

i'm pretty new to Qlikview and am trying to get my head round set analysis. I have a table with 2 columns essentially. One with a date value and the other with a monetay value (Revenue).

I can display a bar chart showing revenue by week, but this shows all data historically going back years. I only want to show the last 6 weeks though on a particular chart, whilst retaining all the data for previous years in the background for another chart.

currently my expression is simply SUM(Revenue). Would I do a set analysis to only display the last 6 weeks? If so, how?

19 Replies
Not applicable
Author

hang on. Now it's displaying the next 4 weeks in the future. Obviously all the values are zero. Why would it do that?

Nicole-Smith

You must have dates for 4 weeks in the future.

Try this:

sum({<CalendarDate={'>=$(=date(weekstart(max(CalendarDate)-42)))<=$(=date(weekend(today())))'}>}Revenue)

Not applicable
Author

Awesome helpful scenariou

Not applicable
Author

HI Nicole,

in fact, i'm still not getting those the weeks with zero revenue to display. My full expression is:

SUM({<CalendarDate={'>=$(=date(weekstart(max(Today())-42)))<=$(=date(weekend(today())))'}>}TradeBrokerageLocal)

The rows for the missing dates do exist in the table, it's just that the cells are null, but i seem to be excluding these

alex

Not applicable
Author

Sorry, to be clear, the cells have zeros in them

Not applicable
Author

Should I be using the IF function, instead of a set analysis?

Nicole-Smith

Not applicable
Author

Actually, sorry this is not correct. Apologies for the confusion.

Essentially i have transactions with a date, however there is not a transaction for every date. How would i 'fill in' those missing dates to display on a time-based bar chart?

Nicole-Smith

You will need to adapt it to fit your data, but this should do the trick:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Not applicable
Author

Thanks Nicole. Although as I'm a Qlikview beginner, that post is a bit advance for me and I wouldn't know how to edit that script or where to put it!

I'll have a look round the forums and see if there is anything clearer