12 month rolling sum

7KD — Mon, 04 Dec 2023 23:22:23 GMT

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.

Re: 12 month rolling sum

7KD — Tue, 05 Dec 2023 03:18:20 GMT

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.

topic Re: 12 month rolling sum in App Development

12 month rolling sum

Re: 12 month rolling sum