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

[Anonymous]

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

[tresesco]

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.

[Anonymous]

Hi Treseco,

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

Thanks

Manish Arora

[tresesco]

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

-474,014 and 15,386 ?

[Anonymous]

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

[tresesco]

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