Montly "Trimester" aggregation

[rafaeltcosta]

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.

[maxgro · Accepted solution]

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-oth...

[rafaeltcosta]

Thank you! I will check it.

[Clever_Anjos]

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

[rafaeltcosta]

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"