Formula for previous months avg. - LAG ? CALCULATE ?

[Former Member]

Hi all

I Want to make a KPI which uses the average of the last 6 months for a certain parameter.

So I need to create a virtual variable which should be equal to the avg. of past 6 months and then put in the formula to get desired results.

It should give correct result when viewed on - monthly, quaterly, yearly basis. (in all cases virtual variable should give the avg of last 6 months and then the sum/avg, time consolidation to be applied on KPI)

[Former Member · Accepted solution]

Hi there,

Try checking the online Help in PAS for the MOVING2() command.

You can use it in combination with a CALCULATE instruction to save the result in a numeric metric. So you need to create this new numeric metric, lets say, KPI_A with monthly periodicity and time consolidation LAST, select the period for which you want the values to be calculated and then in IDQL issue the command:

CALCULATE KPI_A = MOVING2(<variable>, <type> ,6)

The <variable> will be the metric with the original values on which you want to calculate this average and type is to specify whether to include or exclude missing observations in the average, or to return a missing value if at least one observation is missing.

The last parameter is the one where you will define that you want to use previous 6 months for this calculation.

This is more detailed in the PAS Help.

Hope this helps!

Best regards,

Ricardo Vieira

[Former Member · Accepted solution]

Hi Ricardo

I did as told by you.

X = Moving2 (Y,2,9)

Results are coming correctly for Monthly and Also for Yearly in some cases.

But results for 'Quaterly' and 'Year to date' are still very much out of the way. (sometimes 10 times more than expected results).

[Former Member · Accepted solution]

Hi Ricardo

I did as told by you.

X = Moving2 (Y,2,9)

Results are coming correctly for Monthly and Also for Yearly in some cases.

But results for 'Quaterly' and 'Year to date' are still very much out of the way. (sometimes 10 times more than expected results).

[Former Member · Accepted solution]

Hi,

Your variable should return the average for the last 6 months, regardsless of the periodicity you choose, right?

This means that this variable has the have time consolidation set to LAST. It cannot be a VIRTUAL variable, it has to be a NUMERIC measure which gets its values by the CALCULATE command I mentioned above.

Hope this helps!

Ricardo

[Former Member · Accepted solution]

Hi Ricardo

I have made it a NUMERIC measure and it seems to be working fine now. Thnks for that.

But I am still curious to know about the difference in making it virtual and numeric measure. Why was it not working as virtual measure.

Please explain the Paradox.