Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create new rangesum expression from another pivot table

Hello!

I hope someone can help me on this..

I have a rangesum expression  labeled as [Net Forecast] in a pivot table where formula is

RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty))


  This is my output  

MaterialWeek10/19/201510/26/201511/02/201511/09/201511/16/201511/23/2015
AS-123 -2000-3202-44826238-5042-6322


Now I want to create a  new pivot table  using the expression from the other pivot table where if the qty is  > 1  in weekly week buckets, make it  0, if the qty is negative then convert to positive qty...


My desired output

MaterialWeek10/19/201510/26/201511/02/201511/09/201511/16/201511/23/2015
AS-123 200032024482050426322


Hoping a response for this.

2 Replies
aadil_madarveet
Partner - Creator II
Partner - Creator II

try this..

IF(

RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty)) > 1,

0,

Fabs(

     RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty))

     )

)

Anonymous
Not applicable
Author

Hi! Mohammed,

Thanks you for your response but I tried it but looks different :  From 10/19-11/02 - it's not doing a range sum ...11/9 is ok  but in 11/16, the value from 11/09 6238  is adding the value  with 11/16 5042.

Tks.

Results :

 

MaterialWeek10/19/201510/26/201511/02/201511/09/201511/16/201511/23/2015
AS-123 2000120212800112801280