Straight Table Calculations

[Anonymous]

Hello ! I am very new to QV.

Please see attached..

the dispersion work great on the individual months, but the aggregate accuracy/dispersion isn't working correctly.

The dispersion needs to be the 'sum of the rows of the ABS(error) / total Lag3 forecast.

Help,,,,

[Anonymous]

The dispersion should be 57%, not 20%

[dplr-rn]

Whats the expression for the column and also what have you set the total mode as?

a sample app would help

[sunny_talwar]

What are your expressions for ABS and Lag3? Wrap them around with Sum(Aggr(, Dimensions)) and it should work fine

Sum(Aggr(ABSExpression, YourDimensions))/Sum(Aggr(Lag3Expression, YourDimensions))

[Anonymous]

Actual Sales = Sum ([Actual Sales])

Lag3 Forecast = Sum ([Lag3 Forecast])

ABS Error = fabs(Sum ([Actual Sales])-Sum ([Lag3 Forecast]))

Accuracy = Sum ([Actual Sales])/Sum ([Lag3 Forecast])

Dispersion = (fabs(Sum ([Actual Sales])-Sum ([Lag3 Forecast]))) /( Sum ([Lag3 Forecast]))

[Anonymous]

I do not see how to upload the QV

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(fabs(Sum ([Actual Sales])-Sum ([Lag3 Forecast])), [*DmdUnit], [Actual Month]))/Sum ([Lag3 Forecast])

[Anonymous]

Perfect !

I assume that if I remove the DmdUnit and Actual Month's fields from the table - it will no longer work ?

Let's say I only want a simple table that just shows that accuracy and dispersion for the selected dmdunit and months - but not actually in the table ?

[sunny_talwar]

Not sure I follow, you want to show this in a text box object?

[jensmunnichs]

If you remove the DmdUnit and Actual Month fields as dimensions the calculations should still work, you just won't be able to see the accuracy and dispersion per Dmdunit/Actual Month combination anymore, just the total. But if you only want to see these 2 values then you might be better off using text box objects as Sunny suggested, that might look a bit better.