Pivot chart with valuelist dimension & expression based on valuelist

imac — Tue, 10 Jul 2018 13:36:45 GMT

Hi everyone

Very new to Qlik and I am basically learning on my own, so any help is greatly appreciated!!

I have a data table like so with number of days late by period. This table is in flux and both the period and late values can change between reloads.

Period	Late
May-17	3
Jun-17	2
Jul-17	5
Aug-17	2
Apr-17	12
Oct-17	5
Dec-17	9
Mar-18	5

As you can see there are a few missing periods. What I am attempting to do is in a chart, have Period as the Header column and days late as the result, but I am trying to fill in the missing periods so the resulting chart should have the last 12 periods always and look like this:

Period	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
Late	5.0	2.0	-	5.0	-	9.0	-	-	5.0	-	-	-

I attempted to do this in a chart using a calculated dimension like so, which gives me the top row

=valuelist(date(monthstart(today()-365),'MMM-YY'),date(monthstart(today()-330),'MMM-YY'),date(monthstart(today()-300),'MMM-YY'),date(monthstart(today()-270),'MMM-YY'),

date(monthstart(today()-240),'MMM-YY'),date(monthstart(today()-210),'MMM-YY'),date(monthstart(today()-180),'MMM-YY'),date(monthstart(today()-150),'MMM-YY'),

date(monthstart(today()-120),'MMM-YY'),date(monthstart(today()-90),'MMM-YY'),date(monthstart(today()-60),'MMM-YY'),date(monthstart(today()-30),'MMM-YY'))

But short of writing massive if statements to get the bottom row in the expression, I am at a loss on how best to do this. I thought about doing this in 24 text boxes, but it would be pretty cool if there was a way to do this in a chart

Help!

Re: Pivot chart with valuelist dimension & expression based on valuelist

MarcoWedel — Tue, 10 Jul 2018 20:37:39 GMT

Do you have a master calendar linked to your Period field?

Re: Pivot chart with valuelist dimension & expression based on valuelist

imac — Wed, 11 Jul 2018 05:17:57 GMT

No Marco I dont

Added the calendar and now it shows up as expected.

Not knowing what a master calendar is yet, it was a good learning experience

Thanks!!

topic Re: Pivot chart with valuelist dimension & expression based on valuelist in QlikView

Pivot chart with valuelist dimension & expression based on valuelist

Re: Pivot chart with valuelist dimension & expression based on valuelist

Re: Pivot chart with valuelist dimension & expression based on valuelist