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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.