Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeet_007
Partner - Contributor II
Partner - Contributor II

12 Month Rollling Sales but with Different Max Date available for different Shops

Hello Experts @sunny_talwar , @hic , @swuehl ,  @Taoufiq_Zarra  

I have 3 shops

Shop 1 having sales data from 1/1/2014 to 1/12/2018
(5 Years data)

Shop 2 having sales data from 1/1/2014 to 1/8/2014
(8 months data )


Shop 3 having sales data from 1/1/2018 to 1/5/2019
(1 year +5Months data)

I want to calculate last 12 months Sales against dimension Shop and should take Max(SalesDate) according to that shop having their max date available and not from overall dataset.

Expression used:-

Sum({<Year=,Month=Shop=,

MonthYear={"$(='>=' & Date(AddMonths(Max(SalesDate), -11),'MMM-YY') & '<=' & Date(AddMonths(Max(SalesDate),0),'MMM-YY') ) "}>}Sales)

But this expression is fetching sales for overall min to max date
1/6/2018 to 1/5/2019

I need sales for
Shop 1 from
1/1/2018 - 1/12/2018

Shop 2 from
1/1/2014 to 1/8/2014

Shop 3 from
1/4/2018 to 1/5/2019

I have tried with every identifier {1} , 1<> , {$}

I have read an excellent blog similar to my requirement by @sunny_talwar 

Link: How to do Rolling 12 months for latest 12 months with Two Dimensions: 

But it is working fine if all shops have same Max Date and in my case Different Shop have different Period of Sales Data.

Jeet_007_0-1601028085689.png

The  Data and Qvf is attached with the post

2 Replies
OmarBenSalem

try max (total <Shop> YearMonth)

 Capture.PNG

sunny_talwar

Try this

Sum(Aggr(If(YearMonth >= Alt(Max(TOTAL <Shop> YearMonth, 12), Min(TOTAL <Shop> YearMonth)), Sales), Shop, YearMonth))