Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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?

1 Solution

Accepted Solutions

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.

19 Replies
Not applicable

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

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.

Not applicable

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)

Not applicable

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.

Not applicable

Re: Only display last 6 weeks

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

best regards

Chris

Not applicable

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.

Not applicable

Re: Only display last 6 weeks

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

thanks for your help!

Community Browser