topic Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!! in QlikView

Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

Anonymous — Thu, 27 Sep 2018 03:33:44 GMT

Hi Team,

Need Your help please !!!

I have to create/calculate one Column/Measure based on other Measures in Pivot Table/Straight Table.

Have Attached the data in xls file and the screen shot for that output column/Measure which is required to create..

And the formula is

Output =Sum(Value)+ Sum(Open)-Sum(Dmd)-Sum(Fcast)

Also for every Row, need to add the above values of (Sum(Open)-Sum(Dmd)-Sum(Fcast)) into Sum(Value)

Thanks

Manish Arora

Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

tresB — Thu, 27 Sep 2018 04:42:47 GMT

It's seem that you are using a pivot table where few columns are expressions. You could try the below expression:

=RangeSum(Value,-Dmd, Open,-fcast) // assuming Dmd, Open, fcast - are labels to your expressions

If this doesn't help. Try to share your sample app.

Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

Anonymous — Thu, 27 Sep 2018 05:19:01 GMT

Hi Treseco,

I have tried the above code but could not get the expected result.I have sample app attached here.

Thanks

Manish Arora

Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

tresB — Thu, 27 Sep 2018 05:28:12 GMT

May be I haven't understood the calculation logic entirely. Could you explain how you got the two figures:

-474,014 and 15,386 ?

Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

Anonymous — Thu, 27 Sep 2018 06:08:31 GMT

Here is the formula for that Numbers from Data set or Qlik app:- -474014 =Sum(Above Value)-Sum(Dmd)+Sum(Open)-Sum(Fcast) (Output =Sum(Above Value)+ Sum(Open)-Sum(Dmd)-Sum(Fcast)) -474,014= -192809 - 48000 + 0 -233205 15,386 = 57,635 - 0 + 0 - 42,249 Thanks Manish Arora

Re: Pivot / Straight Table - Rangesum - Aggr Output Calculation !!!

tresB — Thu, 27 Sep 2018 06:52:51 GMT

There should be an easier expression than what I found so far. Somebody else might come up with the easier one; I would wait for. Here is mine:

RangeSum(Value, Above(total RangeSum(-Sum(Dmd), Sum(Open),-Sum(fcast)),0,Aggr(NODISTINCT RowNo(),Region, ID, Size, Location_ID, Product_ID, Date)))