topic Re: Straight Table Calculations in QlikView

Straight Table Calculations

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Straight Table Calculations

Anonymous — Thu, 25 Oct 2018 19:10:20 GMT

The dispersion should be 57%, not 20%

Re: Straight Table Calculations

dplr-rn — Thu, 25 Oct 2018 19:40:45 GMT

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

a sample app would help

Re: Straight Table Calculations

sunny_talwar — Thu, 25 Oct 2018 19:41:05 GMT

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

Re: Straight Table Calculations

Anonymous — Thu, 25 Oct 2018 19:53:13 GMT

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

Re: Straight Table Calculations

Anonymous — Thu, 25 Oct 2018 19:54:10 GMT

I do not see how to upload the QV

Re: Straight Table Calculations

sunny_talwar — Thu, 25 Oct 2018 19:55:19 GMT

Try this

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

Re: Straight Table Calculations

Anonymous — Thu, 25 Oct 2018 20:01:35 GMT

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 ?

Re: Straight Table Calculations

sunny_talwar — Fri, 26 Oct 2018 00:50:01 GMT

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

Re: Straight Table Calculations

jensmunnichs — Fri, 26 Oct 2018 06:57:59 GMT

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.

Re: Straight Table Calculations

Anonymous — Fri, 26 Oct 2018 12:12:14 GMT

Table works great with new formula.

Now, I copy the table and remove the dmdunit and actual month:

Sales, Forecast are correct, but the ABS Error is only showing the difference.. it should be the sum of the months selected (but not in the table). I assume the formula needs to change :

@sunny talwar - Can you assist again on this formula?

Re: Straight Table Calculations

sunny_talwar — Fri, 26 Oct 2018 12:16:27 GMT

So, the issue is ABS Error expression now and not Dispersion, right? Try this for ABS Error

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

Re: Straight Table Calculations

Anonymous — Fri, 26 Oct 2018 12:25:49 GMT

Beautiful !!!!

Thank you very much... You have saved the day !

Re: Straight Table Calculations

jensmunnichs — Fri, 26 Oct 2018 12:27:43 GMT

Alan,

Have a look at this blog post. I think it'll explain what's happening in your total row.

Totals in Charts

TL;DR; unless specified otherwise, the total line in a table will try to calculate the expression over the full set of data in your table. So in this case it would sum all actual sales and sum all forecasted sales and calculate the difference. If you want it to show sum instead, use Sunny's expression instead or switch the 'total mode' in the expression tab to 'sum of rows' for this expression.

Re: Straight Table Calculations

sunny_talwar — Fri, 26 Oct 2018 12:57:37 GMT

Glad to help. Please close the thread by marking correct and helpful responses.

Best,

Sunny