topic Re: How to use a single combo chart to get Last 12 month vs Last Year Last 12 Month View in App Development

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

AmitSah — Sat, 16 Nov 2024 19:10:30 GMT

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

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

sunny_talwar — Wed, 05 Feb 2020 13:47:38 GMT

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

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

AmitSah — Wed, 05 Feb 2020 14:36:36 GMT

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?

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

sunny_talwar — Wed, 05 Feb 2020 15:07:47 GMT

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?

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

AmitSah — Wed, 05 Feb 2020 15:22:01 GMT

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

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

AmitSah — Thu, 06 Feb 2020 09:25:39 GMT

Hi Sunny,

Any luck with that calculation?

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

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

sunny_talwar — Thu, 06 Feb 2020 11:46:59 GMT

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

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

AmitSah — Thu, 06 Feb 2020 11:53:25 GMT

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

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

sunny_talwar — Thu, 06 Feb 2020 12:57:20 GMT

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

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

AmitSah — Thu, 06 Feb 2020 13:17:14 GMT

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?

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

sunny_talwar — Thu, 06 Feb 2020 13:50:21 GMT

Month_Desc might be a test field... you need to sort the data in order to make sure the Above function is pulling down the records in the correct order.