Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
hang on. Now it's displaying the next 4 weeks in the future. Obviously all the values are zero. Why would it do that?
You must have dates for 4 weeks in the future.
Try this:
sum({<CalendarDate={'>=$(=date(weekstart(max(CalendarDate)-42)))<=$(=date(weekend(today())))'}>}Revenue)
Awesome helpful scenariou
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
Sorry, to be clear, the cells have zeros in them
Should I be using the IF function, instead of a set analysis?
Can you post a sample .qvw?
See Preparing examples for Upload - Reduction and Data Scrambling
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?
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
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