topic Re: Making chart dimensions using calendar variable in QlikView

Making chart dimensions using calendar variable

Anonymous — Tue, 19 Dec 2017 15:30:16 GMT

Dear Colleagues,

Id like to create a chart which dimensions are based on a calendar variable. The dimensions should show the IDs that happened from the chosen date, 30 days before the chosen date and so on..

ID	Date
1	01/12/2017
2	01/11/2017
3	11/10/2017
4	12/09/2017

Let´s suppose that we chose the day 19/12/2017.

The dimensions should show then "19/12/2017, 19/11/2017 ... 19/12/2016" (13 dimension values).

The expression should be a simple count distinct ID.

Can someone help me out?

Thx!

Re: Making chart dimensions using calendar variable

sunny_talwar — Tue, 19 Dec 2017 15:35:39 GMT

I think all your need is set analysis in your expression

Dimension

Date

Expression

Count(DISTINCT {<Date = {"$(='>=' & Date(Max(Date) - 30, 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY'))"}>} ID)

Also, look here

Dates in Set Analysis

Re: Making chart dimensions using calendar variable

Anonymous — Tue, 19 Dec 2017 16:06:33 GMT

Sunny, how many dimensions will I have in the chart you are proposing?

Re: Making chart dimensions using calendar variable

Anonymous — Tue, 19 Dec 2017 16:06:51 GMT

I mean.. how many values in the dimension axis.

Re: Making chart dimensions using calendar variable

sunny_talwar — Tue, 19 Dec 2017 16:13:48 GMT

It should show all available dates between max date and max date - 30

Re: Making chart dimensions using calendar variable

Anonymous — Tue, 19 Dec 2017 16:23:22 GMT

Ok, as I checked.. But the aim is to create 12 dimesion values.

The first one would show values between max date and max date-30, the second one shows between max date-30 and max date -60 and so on..

Re: Making chart dimensions using calendar variable

sunny_talwar — Tue, 19 Dec 2017 16:27:20 GMT

Then you can either create a new dimension in the script or you can use a calculated dimension

If(Date > Max(TOTAL Date) - 30 and Date <= Max(TOTAL Date), 'max date - max date - 30',

If(Date > Max(TOTAL Date) - 60 and Date <= Max(TOTAL Date) - 30, 'max date - 30 - max date - 60',

and so on....