Only display last 6 weeks

[#1]

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?

[#2]

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

[Nicole-Smith]

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.

[#4]

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)

[#5]

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?

[Nicole-Smith · Accepted solution]

Two of them:

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

The other one is to format the string as a date.

[#7]

You can then use instead the calculated dimension and test if (date <= today()-42, week)

best regards

Chris

[#8]

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?

[Nicole-Smith]

Uncheck "Suppress Zero Values" on the Presentation tab.

[#10]

tried that initially, but i also had to uncheck 'show all values' on the dimensions tab

thanks for your help!