# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
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 Numbers Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8 Wk9 Wk10 Wk11 Wk12 Wk13 Demand 0 1000 0 2000 5000 1000 2000 0 2000 1000 1000 3000 2000 Supply 1000 0 0 5000 0 0 2000 0 8000 0 2000 0 Variance 1000 0 0 -2000 0 -1000 -2000 2000 0 7000 6000 5000 3000 Formula for Variance Supply-Demand If prev week > 0, Supply - Demand +prev week,Supply - Demand) same as Wk2 same as Wk2 same 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 No Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 MonWeek 02/29/2016 03/07/2016 03/14/2016 03/21/2016 03/28/2016 04/04/2016 04/11/2016 04/18/2016 04/25/2016 Variance 1,000 0 0 -2,000 -2,000 -3,000 -5,000 -6,000 -8,000

5 Replies
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 ...)

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.

MVP

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

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

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 Numbers Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8 Wk9 Wk10 Wk11 Wk12 Wk13 Demand 0 1000 0 2000 5000 1000 2000 0 2000 1000 1000 3000 2000 Supply 1000 0 0 5000 0 0 2000 0 8000 0 2000 0 Variance 1000 0 0 -2000 0 -1000 -2000 2000 0 7000 6000 5000 3000 Formula for Variance Supply-Demand If prev week > 0, Supply - Demand +prev week,Supply - Demand) same as Wk2 same as Wk2 same as Wk2

MVP

Maybe like this?

Tags