Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Accumulate values until next occurrence

Invoice NoInvoice LineMTO_Key%Cus_Ref%Prod_CodeValueMTO
1080867710808677*110808677-1CHO006316560.001
1080867710808677*210808677-0CHO006EXOG-CURL-1522961.520
1080867710808677*310808677-0CHO006EXOG-CUSH-PIP435.580
1080867710808677*410808677-1CHO006318440.001
1080867710808677*510808677-0CHO006EXOC-CUSH-PIP434.310
1080867710808677*610808677-0CHO006EXOC-TIEB-PIP7.520
1080867710808677*710808677-0CHO006SPEC-CARRIAGE8.000

Hi, in the table above I need to accumulate the value in field Value until the the next occurrence of MTO and then apply the accumulated value to the lines where MTO = 1. So the end result should see line 1 = 67.10 (line1 + line2 + line3), and line 4 as 19.83 (line4 + line5 + line6 + line7). I have tried various attempts using previous and peek without success. Any suggestions would be most welcome.

4 Replies
Highlighted

Re: Accumulate values until next occurrence

Try this

Table:

LOAD * INLINE [

    Invoice No, Invoice Line, MTO_Key, %Cus_Ref, %Prod_Code, Value, MTO

    10808677, 10808677*1, 10808677-1, CHO006, 31656, 0.00, 1

    10808677, 10808677*2, 10808677-0, CHO006, EXOG-CURL-15229, 61.52, 0

    10808677, 10808677*3, 10808677-0, CHO006, EXOG-CUSH-PIP43, 5.58, 0

    10808677, 10808677*4, 10808677-1, CHO006, 31844, 0.00, 1

    10808677, 10808677*5, 10808677-0, CHO006, EXOC-CUSH-PIP43, 4.31, 0

    10808677, 10808677*6, 10808677-0, CHO006, EXOC-TIEB-PIP, 7.52, 0

    10808677, 10808677*7, 10808677-0, CHO006, SPEC-CARRIAGE, 8.00, 0

];


FinalTable:

LOAD *,

If([Invoice No] = Previous([Invoice No]),

If(Previous(MTO) = 1, Value, RangeSum(Peek('NewValue'), Value)), Value) as NewValue

Resident Table

Order By [Invoice No], [Invoice Line] desc;


DROP Table Table;

Highlighted
Partner
Partner

Re: Accumulate values until next occurrence

Thanks Sunny, I'll give it try and revert

Highlighted
Partner
Partner

Re: Accumulate values until next occurrence

Hi Sunny, works a treat thanks!!

Highlighted

Re: Accumulate values until next occurrence

No problem at all