Problem on Chart Comparing Current Month & Same Month last year, when using date as a Filter.

[faridborbar]

Hi Everybody!!

I started using QlikSense not long ago.

I have already implemented a Combined Chart with Sales over TIME (MMM YY). On this same Chart I needed to show the Sales variation percentage in relation to the same Month but one year ago. So for this I had to implement an AsOfCalendar. You can see the results in the photo.

So for Nov 19, you will see the amount of sales in Nov 19, and the % variation between Nov19 and Nov18.

And so on for every date display in the chart.

The Set Analysis for the % variation is :

(sum({<AsOfMonth={">=$(=$(vMAAA)) <=$(=$(vMA))"},MonthDiff={'0'}>} Sales)
-sum({<AsOfMonth={">=$(=$(vMAAA)) <=$(=$(vMA))"},MonthDiff={'12'}>} Sales))
/sum({<AsOfMonth={">=$(=$(vMAAA)) <=$(=$(vMA))"},MonthDiff={'12'}>} Sales)

and for Sales:

sum({<TIME={">=$(=$(vMAAA)) <=$(=$(vMA))"},MonthDiff={'0'}>} UF)

Where vMA = date(Max(TIME),'MMM YY')  and  vMAAA = date(addmonths($(vMA),-12)) 

//Because the client only wants to see the last 12 months in the Chart.

And my Dimension is AsOfMonth, which is defined using the TIME field.

So far so good. Now the client want to add the TIME parameter as a Filter so that They will be able to select the date to be Analyzed, and the chart should change according to it. Therefore, if we select Ago 19   , the chart should display both mesuares, From Ago 18 to Ago 19, and each % variation accordingly . 

This is what I get

Well, AsOfMonth is a different field, so I Understand that is not gonna work when applying seleccions over TIME.

In a Paralel situation , To any other Chart which doesn't use AsOfMonth as Dimension, but TIME instead, I can make the Time filter work perfectly, like this:

Does anyone have any idea of how to implement both scenarios together?

I know it is possible to find Set Analysis approaches to get Previous Month Comparissons  using Aggr() and Above() function and keep using TIME as dimension, but I personaly haven't been able to implement them and I am not sure if it will work.

Any help, guidance or suggested approach will be  waaaaaaay appreciated

Best regards and hughs!

Farid Borbar