19 Replies Latest reply: Jan 10, 2014 2:59 AM by Alex Hamilton McLeod

# 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?

• ###### Re: Only display last 6 weeks

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

• ###### Re: Only display last 6 weeks

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)

• ###### Re: Only display last 6 weeks

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

best regards

Chris

• ###### Re: Only display last 6 weeks

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.

• ###### Re: Only display last 6 weeks

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?

• ###### Re: Only display last 6 weeks

Two of them:

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

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

• ###### Re: Only display last 6 weeks

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?

• ###### Re: Only display last 6 weeks

Uncheck "Suppress Zero Values" on the Presentation tab.

• ###### Re: Only display last 6 weeks

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

• ###### Re: Only display last 6 weeks

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

• ###### Re: Only display last 6 weeks

You must have dates for 4 weeks in the future.

Try this:

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

• ###### Re: Only display last 6 weeks

HI Nicole,

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

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

• ###### Re: Only display last 6 weeks

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

• ###### Re: Only display last 6 weeks

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

• ###### Re: Only display last 6 weeks

Can you post a sample .qvw?

• ###### Re: Only display last 6 weeks

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?