Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.