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
Creator II
Creator II

Adding the Previous Week or Rangesum Question

Hello!  Pls. help me solve my rangesum formula.   I have a dimensional field that I make them into 60 weekly buckets.  I'm trying to compute the net variance as shown below :

   

Week NumbersWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9Wk10Wk11Wk12Wk13
Demand0100002000500010002000020001000100030002000
Supply100000 500000200008000020000
Variance100000-20000-1000-2000200007000600050003000
Formula for VarianceSupply-DemandIf prev week > 0, Supply - Demand +prev week,Supply - Demand)same as Wk2same as Wk2same as Wk2

I tried using the rangesum formula but the results is different from what I want to achieve. If you see below Week 5 , it's -2000 since the -2000 from week 4, got carried over...This should be 0 as shown in the above table.

   

Week NoWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9
MonWeek02/29/201603/07/201603/14/201603/21/201603/28/201604/04/201604/11/201604/18/201604/25/2016
Variance1,00000-2,000-2,000-3,000-5,000-6,000-8,000

Thank you in advance.

5 Replies
Highlighted
MVP
MVP

Try a straight table chart with dimension WeekNo and as expression

=Rangesum(Above( Sum(Supply),0,Rowno() )) - Rangesum(Above( Sum( Demand),0,Rowno() ))

If you are using a pivot table chart, you may need to use Before() instead of Above(). If you add more dimensions. you may need to add the TOTAL qualifier to RowNo(TOTAL) and Above(TOTAL ...)

Highlighted
Creator II
Creator II

hi! Swuehl,

I tried the straight table the results are still different....but I need the results in pivot table though...

I tried to replace above with before since I want to be in pivot table but I get a   different results too.  Also, would appreciate if you could givethe exact formula with the Total qualifier if I add another 2 dimensions like Monweek and Materials... Sorry but this is new to me and first time to do such a thing.

Thank you for your prompt response.

Highlighted
MVP
MVP

Ok, if you want to use a pivot table, you also may need to change RowNo() to ColumnNo() if pivoted.

I used this to load in your excel sheet data:

LOAD [Week Numbers],

    Demand,

    Supply

FROM

(ooxml, embedded labels, table is Sheet2, filters(

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Transpose()

));

Then I created a pivot table chart with dimension Week Numbers and as expression:

=Rangesum(Before(Sum(Supply),0, ColumnNo() )) -RangeSum(Before(Sum(Demand),0,ColumnNo()))

Highlighted
Creator II
Creator II

Swuehl,

I got the right numbers using the formula...But is there any way to add condition that if previous week > 0 ,

Supply - Demand + Prev Week  (current rangesum formula)....If Previous week < 0 ,  Supply - Demand....The rangesum should stop if the previous week is > 0, then start the rangesum again...

Tks.

Week NumbersWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9Wk10Wk11Wk12Wk13
Demand0100002000500010002000020001000100030002000
Supply100000500000200008000020000
Variance100000-20000-1000-2000200007000600050003000
Formula for VarianceSupply-DemandIf prev week > 0, Supply - Demand +prev week,Supply - Demand)same as Wk2same as Wk2same as Wk2

Highlighted
MVP
MVP

Maybe like this?