Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 

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

Hello Experts @sunny_talwar , @Henric_Cronström , @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:-


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.


The  Data and Qvf is attached with the post

2 Replies

try max (total <Shop> YearMonth)


Try this

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