How to use a single combo chart to get Last 12 month vs Last Year Last 12 Month View

[AmitSah]

Hi Experts,

I am facing one issue related to Set Analysis Logic. I am new to Qlik Sense and trying to Figure out a way for my requirement.
I have an As of Date selector and below one Table which Contains Month and 2 Measures. ( 1. Last 12 month Gross Sales , 2. LY Last 12 Month Gross Sales) these 2 measures need to be calculated on the basis of the Date selected from filter pane.
Example: If I selected 2017/07/31 from As of Date selector then the last 12 months should show from that date and those 2 measures should also calculate on the basis of that.
So, I have created a Last 12 Month Measure with below Set Analysis logic:

Measure i.e

Last 12 month Gross Sales = Sum({$<Date_Derived={">=$(=Date(addmonths(Date(max(Date_Derived),'YYYY/MM/DD'),-12),'YYYY/MM/DD'))<=$(=Date(max(Date_Derived),'YYYY/MM/DD'))"}>} Gross_Sales)

and when I pulled this measure to my Chart then it is giving me correct last 12 month on the basis of the Date Selection.

But I also want to get the LY Last 12 months, for that, I thought of creating another measure with below Logic but it is including another month from LY. So, this is not working as per my requirement. So, I removed it from my Chart. The below chart is not included this Measure.

Last Year - Last 12 Months Gross Sales = Sum({$<Date_Derived={">=$(=Date(addmonths(AddYears(Date(max(Date_Derived),'YYYY/MM/DD'),-1),-12),'YYYY/MM/DD'))<=$(=Date(AddYears(Date(max(Date_Derived),'YYYY/MM/DD'),-1),'YYYY/MM/DD'))"}>} Gross_Sales)

Need to get LY same month value on "Last Year - Last 12 Months Gross Sales" Measure

Can anyone help me with this requirement?

Any Help will be appreciated.

Thanks,

Amit

[sunny_talwar]

You will either need to use Above() function to do this or use The As-Of table 

[AmitSah]

Thanks for the response but both the method is not going to work for me in this scenario. I want to get the Last year- last 12 month value also in the same chart. example:

Date Selected from Filter Pane: 2017/07/31

Month            TY SALES              LY SALES

AUG 16           105396                  90876

SEPT 16         3789295                3709875

..................

.................

JUN 17              3787621              278768

JULY 17             3072573             246789 -----> THIS VALUE IS FOR JULY 16 AND SAME FOR OTHER MONTH IN THIS COLUMN 

You can see that as I selected 2017/07/31 date from filter pane my month started from July 17 till Aug 16  and the first measure is giving me the value for the shown month i.e from July 17 till Aug 16 and 2nd column is showing data for LY same months. i.e JULY 16, JUN 16..........  AUG 15.

So, as I posted earlier my Set Analysis formula. how can I accomplish the 2nd column in the same chart?

[sunny_talwar]

I don't think this is doable using set analysis... are you able to share some sample data to help you see how this can be done using Above/As-Of Table approaches?

[AmitSah]

Hi, Sure Please find attached QVF file for the same.

[AmitSah]

Hi Sunny,

Any luck with that calculation?

I am attaching QVD Files also which are used in the above post QVF file.

[sunny_talwar]

Will take a look now... Also, don't see any qvd attached here

[AmitSah]

Not sure, why I am not able to attach QVD files. Please see if that QVF file will work for you.

[sunny_talwar · Accepted solution]

Try this

Only({$<Date_Derived={">=$(=Date(addmonths(Date(max(Date_Derived),'YYYY/MM/DD'),-11),'YYYY/MM/DD'))<=$(=Date(max(Date_Derived),'YYYY/MM/DD'))"}>} Aggr(
	Above(
    	Sum({<Date_Derived>}Gross_Sales)
	, 12)
, (Month_ID, (NUMERIC))))

[AmitSah]

Hi Sunny,

It is working like a charm, but one question I am having i.e you have used Month_ID as one of the parameters in Agg function. So, why only Month_ID because When I am using Month_Desc then the value is dislocating. Could you please help me understand this?