topic Re: Only show the last 12 months in QlikView

Only show the last 12 months

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

Im creating a chart which displays the amount of new debtors per month. However, I want to only show the last 12 months in my chart and can't seem to fix it with a dimension limit.

This is the line of code I use to define the debtor start date and also use this as my month filter in the chart:

Load

, Month(Debtor_StartDate) & '/' & Year(Debtor_StartDate) as Debtor_StartMonth

Load

Date(Min(Date_Key)) as Debtor_StartDate

from

[.......]

Thanks in advance

Re: Only show the last 12 months

devarasu07 — Fri, 31 Mar 2017 05:51:54 GMT

Hi,

Try like this,

Last 12 months | QlikView Help, Tips & Hints

Re: Only show the last 12 months

Anil_Babu_Samineni — Fri, 31 Mar 2017 06:16:40 GMT

Dimension limit really won't possible and it won't help for you

Create chart and use dim as month

and expression is

Sum({<Month = {">=AddMonths(Max(Month),-12) <=Max(Month)"}>}Measure)

Re: Only show the last 12 months

Tue, 04 Apr 2017 10:51:25 GMT

I found a solution to my problem
This is what I added into the script and then used a Set Analysis to get in right into the graph.

Script:
If(Date <= Today() And Date >= SetDateYear(Today(), Year(Today())-1), 1, 0) As Last12Months

Set Analysis:

=Count({< Last12Months = {1}>} Debtor_StartDate)

Re: Only show the last 12 months

mboreham — Tue, 07 Nov 2017 12:40:03 GMT

Thank you so much for this Ivo. It looked so easy but have spent all morning working on it.

Re: Only show the last 12 months

KL1 — Wed, 26 Oct 2022 16:38:18 GMT

I understand this is very old question. I thought to share an alternative way to solve this.

I have created a variable to capture max-month in Load script (vMaxMonth) and then used this variable in the table/chart for the dimension as :

=if(Month>Addmonths(vMaxMonth,-12), Month)

Thought to share in case this may help others in the future.