Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Straight Table Calculations

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,,,,

1 Solution

Accepted Solutions
Highlighted

Try this

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

View solution in original post

14 Replies
Highlighted
Contributor
Contributor

The dispersion should be 57%, not 20%

Highlighted
Partner
Partner

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

a sample app would help

Highlighted

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))

Highlighted
Contributor
Contributor

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]))

Highlighted
Contributor
Contributor

I do not see how to upload the QV

Highlighted

Try this

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

View solution in original post

Highlighted
Contributor
Contributor

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 ?

Highlighted

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

Highlighted
Creator III
Creator III

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.