Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
imac
Contributor II
Contributor II

Pivot chart with valuelist dimension & expression based on valuelist

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.

PeriodLate
May-173
Jun-172
Jul-175
Aug-172
Apr-1712
Oct-175
Dec-179
Mar-185

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:

PeriodJul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18
Late5.02.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!

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

2 Replies
MarcoWedel

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

imac
Contributor II
Contributor II
Author

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!!