topic Re: Only display last 6 weeks in QlikView

Only display last 6 weeks

Wed, 08 Jan 2014 15:06:28 GMT

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

Wed, 08 Jan 2014 15:09:06 GMT

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

Nicole-Smith — Wed, 08 Jan 2014 15:09:29 GMT

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

Wed, 08 Jan 2014 15:31:37 GMT

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

Wed, 08 Jan 2014 15:31:46 GMT

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

Nicole-Smith — Wed, 08 Jan 2014 15:32:54 GMT

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

Wed, 08 Jan 2014 15:44:12 GMT

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

best regards

Chris

Re: Only display last 6 weeks

Wed, 08 Jan 2014 15:58:26 GMT

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

Nicole-Smith — Wed, 08 Jan 2014 16:07:47 GMT

Uncheck "Suppress Zero Values" on the Presentation tab.

Re: Only display last 6 weeks

Wed, 08 Jan 2014 16:12:15 GMT

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

thanks for your help!

Re: Only display last 6 weeks

Wed, 08 Jan 2014 16:15:14 GMT

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

Nicole-Smith — Wed, 08 Jan 2014 16:18:42 GMT

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

Wed, 08 Jan 2014 16:38:32 GMT

Awesome helpful scenariou

Re: Only display last 6 weeks

Thu, 09 Jan 2014 17:45:27 GMT

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

Re: Only display last 6 weeks

Thu, 09 Jan 2014 17:53:40 GMT

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

Re: Only display last 6 weeks

Thu, 09 Jan 2014 18:14:05 GMT

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

Re: Only display last 6 weeks

Nicole-Smith — Thu, 09 Jan 2014 18:20:09 GMT

Can you post a sample .qvw?

See Preparing examples for Upload - Reduction and Data Scrambling

Re: Only display last 6 weeks

Thu, 09 Jan 2014 18:28:33 GMT

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?

Re: Only display last 6 weeks

Nicole-Smith — Thu, 09 Jan 2014 18:48:50 GMT

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

Re: Only display last 6 weeks

Fri, 10 Jan 2014 07:59:11 GMT

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