Calculate a measure based on multiple dimensions

[GabrielOtet]

Dear all,

I have a situation which I don't really know how to handle.

• I have as Dimension a field which contain a string that has a long string and inside it 4 digits like YYMM (1901 as an example) representing the year and the month of the data.
• I have as Measure a field that takes from the current Dimension (month) some data and calculate the average of it

The graph shows the points for every month of the year(s), everything fine until this point.

Now, I want to actually calculate the average on previous x months and show it into the current month (Like: avg (current month)+avg(previous month)...Kind of multi-month average.

Unfortunately until now, there is no possibility for me to do it. I'm thinking that maybe preparing a table when loading the data with some informations would help. Did someone have the same problem and managed to solve it until now?

Gabriel

[OmarBenSalem · Accepted solution]

As dimension:

Month

As a measure: try sthing like:

Aggr(

RangeAvg(Above(

Avg(Measure)

,0,1))

,Month)

[GabrielOtet]

in this way it worked.

Anyhow, I have an additional question:

I have implemented the following formula and as far as I can see, there are the same results for both of them, can you maybe let me know what is the difference?

My formula: 

RangeAvg(Above(

Avg(Measure)

,0,1))

[OmarBenSalem]

What this does, it calculate the avg of the actual point of dimension and the one just above it (the previous month)

Now, if u change the sorting of ur chart, the previous Month could be not really the previous one but maybe the next month?

Which will lead to a miscalculation

Thus, when adding the aggregation by Month, u tell Qlik to always consider ur chart to be sorted by Month asc.

So, even if the sorting changes, Qlik'll always consider the above (Month) as the previous one (the real previous one)

Hope this Helps?

[OmarBenSalem]

And before I forget, if sthing works; please accept the response so that everyone will know that this has been asnwered and if u want, like it (this is not for the community members though, this is just to appreciate the effort someone puts to help)

Thanks 🙂 

[GabrielOtet]

Hi Omar,

All clear now, many thanks for the effort you put into it.

All the best, 

Gabriel