topic Re: Montly "Trimester" aggregation in App Development

Montly "Trimester" aggregation

rafaeltcosta — Sat, 16 Nov 2024 03:00:45 GMT

Hello Everyone,

I have the usual montlhy sales. What I need is the trimester (quarter) sales with the beginning of the quarter being the month on the dimension.

Example: Dimension value: 201910 Measure value: 90 million

Explanation: 90 million would be the sum of the actual montlhy values regarding:

201910: 35 million

201911: 30 million

201912: 25 million

Example 2 (continuation): Dimension value: 201911 Measure value: 150 million

Explanation: 150 million would be the sum of the actual montlhy values regarding:

201911: 30 million

201912: 25 million

202001: 95 million

My (not so good) approach: I could (did not actually do this, I'm guessing a horrible performance) load my fact table another 2 times: changing only the reference date to 1 month ahead and then another one 2 months ahead.

My formula would be: sum(actual value)+sum(next month value)+sum(last month value)

I am sure that something could be done via set analysis. I tried a few formulas, Having the next 2 periods in the same line and applying [month]={"$([next month])"}. This is the general idea, but off course it did not work. I want to do the sum described above via set analysis.

Thank you all in advance.

Re: Montly "Trimester" aggregation

maxgro — Wed, 11 Mar 2020 20:01:52 GMT

I think you can find different solutions here (use below instead of above):

https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

Re: Montly "Trimester" aggregation

rafaeltcosta — Wed, 11 Mar 2020 20:10:44 GMT

Thank you! I will check it.

Re: Montly "Trimester" aggregation

Clever_Anjos — Wed, 11 Mar 2020 20:19:29 GMT

I would go with AsOf table, it is more flexible and delivers a good performance

Re: Montly "Trimester" aggregation

rafaeltcosta — Fri, 13 Mar 2020 13:10:26 GMT

Guys,

I solved this in 2 ways: doing exactly what I said it was a not so good solution: loading the fact table putting addmonts(-1) and addmonths(-2). This created a necessity to remove the new 2 minimum dates miscreated. After that, it worked.

A more "classy" solution is using above: I forgot this could be used in graphs too! Using sum(valye)+above(sum(valye),1)+above(sum(valye),2).

Classy but it looses precision. That because when the measure is an avg, I need to do an rangeavg of the avgs. This is not as precise as doing the actual avg of all 3 months at once.

At last, I will try the solution you two suggested.

Thank you very much"