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?
Two of them:
sum({<CalendarDate={'>=$(=date(weekstart(max(CalendarDate)-42)))'}>}Revenue)
The other one is to format the string as a date.
Hi Alex
no need to set analysis to do so but use the dimensional limits
Set analysis would be useful if you wanted to store 6 weeks under 1 value only
best regards
Chris
sum({<Date={'>=$(=date(weekstart(max(Date)-42)))'}>}Revenue)
So for example, if your max(Date) is today, this will show weeks starting with the week starting 11/25/2013.
OK, but that only seems to apply to the Y-axis
i.e. only show the 10 highest weeks by revenue. How would I get it to apply to the x-axis (Date)
There’s 3 ‘Date’ strings there. If my date was called CalendarDate, how would it look? Would I substitute it for all of them or are one or two of those operators?
Two of them:
sum({<CalendarDate={'>=$(=date(weekstart(max(CalendarDate)-42)))'}>}Revenue)
The other one is to format the string as a date.
You can then use instead the calculated dimension and test if (date <= today()-42, week)
best regards
Chris
This worked. Thanks!
Although weeks with zero revenue are completely omitted for some reason. I need to show them still, but as having no value. The 'Suppress when value is null' is unchecked and 'show all values is ticked'. Is there another option i'm missing?
Uncheck "Suppress Zero Values" on the Presentation tab.
tried that initially, but i also had to uncheck 'show all values' on the dimensions tab
thanks for your help!