Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cacosta35
Creator II
Creator II

RangeSum in Script

Hello! I would appreciate if someone can help me on this...

I have an expression in pivot table  that I would like to put it in script :

= rangesum(before(total sum(if(SalesQty >0, SalesQty )),0,NoOfColumns(TOTAL)))

my table is showm below :

Rangesum:
NoConcatenate
load
Product,

Location,

Company,,
WeekDate,

alesQty,

f(Product = previous(Product]), rangesum(SalesQty, peek(Sales_Accu)),SalesQty) as Sales_Accu,

Resident demand;

But  the output doesn't  come out right.  Below is my expected out put  in yellow shade ::

   

ProductLocationCompanyWeekDate3/6/20173/13/20173/20/20173/27/20174/3/20174/10/20174/17/20174/24/20175/1/2017
Product  ATXCom ASales Qty0000183570000
Product  ATXCom ARangesum 00001835718357183571835718357
Product  ATXCom BFcstQty600060004000600040006000600040006000
Product  ATXCom BRangesum 60001200016000220002600032000380004200048000
Product  ATXCom CFcstQty589317111624177543301581162816422329
Product  ATXCom CRangesum 589376049228110031533316914185422018422513

.

Pls. advise...Btw, my expression in pivot table   works perfectly.

Thank you in advance.

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

This should work

if(Product = previous(Product]),

     If(Company= previous(Company), rangesum(SalesQty, peek(Sales_Accu)),SalesQty),SalesQty) as Sales_Accu,

View solution in original post

4 Replies
aarkay29
Specialist
Specialist

Try using Order by

load
Product,

Location,

Company,
WeekDate,

SalesQty,

f(Product = previous(Product]), rangesum(SalesQty, peek(Sales_Accu)),SalesQty) as Sales_Accu,

Resident demand

Order By    

     Product,

     Location,

     Company,
     WeekDate ASC
;

cacosta35
Creator II
Creator II

Hi! Aar,

I tried but it only work on Com A ... 1st week 03/06 Com B and C has different value ...not sure where they come from.

   

aarkay29
Specialist
Specialist

This should work

if(Product = previous(Product]),

     If(Company= previous(Company), rangesum(SalesQty, peek(Sales_Accu)),SalesQty),SalesQty) as Sales_Accu,

View solution in original post

cacosta35
Creator II
Creator II

HI!   Aar,

It still doesn't work but I added  if(Location = previous(location]) and it worked now.  Thank you so much.

Rgds