Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
7KD
Contributor
Contributor

12 month rolling sum

Hi, I have seen rolling sum examples of an accumulated field, however I need to do something similar by product and a condition.

 

Product, Date, SaleCount

A, 11/02/2023, 1

A, 14/04/2023, 1

A, 16/06/2023, 1

B, 16/04/2023, 1

B, 18/04/2023, 1

 

How to do a rolling 12 month, counting products in the last 12 month that have sold more than 2 times.

I want to build a bar chart with months and a count of how many met the condition in the last 12 months. So for example all months after the 11/02/2023 should count 2. The 12th month after the 16/06/2023 would count 0.

Labels (1)
1 Reply
7KD
Contributor
Contributor
Author

UPDATE:

I have tried to create an asoftable as deomnstrated here https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130 which seems to work in the data model ok.

 

however cant get the set analysis right. this is what i have below.

sum(if(aggr(Sum({<MonthDiff={"<12"}>} SaleCount), [Product]) > 2,1))

on my bar graph, a product is only counted on one of the months, not all consecutives months afterwards.