topic Sum 12 months by product in App Development

Sum 12 months by product

HenriqueCoelho — Mon, 26 Sep 2022 13:48:03 GMT

Hi Qlik Community,

I would like some help to develop a 12 months rolling accumulated values, grouped by products on my application.

I have a plenty of data, using the fields 'Month' (returning me MM/YYYY), 'Product' (Name of the product) and 'Amount' (Total value registered by 'Month').

The formula that i'm actually using is:

=sum(aggr(rangesum(above(total sum({<Month=>}[Amount]),0,12)),Month))

The problem is: It only work when I filter by product. While the filter is not active, the accumulated values doesn't appear. There is a way i can "group by" product? Where i can visualize the correct 12 months accumulated values by product, without using the filter.

If there's other way to calculate this, using the data load editor or the expressions, please let me know, any help is appreciated 🙂

Henrique

Re: Sum 12 months by product

deepanshuSh — Mon, 26 Sep 2022 17:15:12 GMT

Hi I am not much sure about the front end, but you can do it in the load editor,

Load

sum(aggr(rangesum(above(total sum({<Month=>}[Amount]),0,12)),Month))

Group by product, month;

Re: Sum 12 months by product

HenriqueCoelho — Tue, 27 Sep 2022 12:33:12 GMT

Hi,

There is no aggr and above functions on the data load editor, so i can't paste the code. I was looking for alternative functions for the data load editor, but didn't find any 😞