Cummulative Calculation based on condition

[#1]

Hi All,

I am stuck in one of the requirement and I am not able to find the right solution.

ID	Seq	Category	Amount
1	1	a	100
1	2	a	200
1	3	a	300
1	4	b	200
1	5	c	150
2	6	c	300
2	7	a	100
2	8	b	100
2	9	b	300
2	10	a	175

I need output like below

ID	Seq	Category	Amount	Cummulative
1	1	a	100	100
1	2	a	200	200
1	3	a	300	300
1	4	b	200	400	“(100+200+300)-200”
1	5	c	150	850	“(100+200+300+400)-150”
2	6	c	300	300
2	7	a	100	2050	“(100+200+300+400+850+300)-100”
2	8	b	100	4400
2	9	b	300	300
2	10	a	175	8725

So basically when Category is Same, amount will be same, but when category changed sequentially (eg. Seq=4) , amount should be calculated as (sum of all previous category amount - current category amount)

Hope I have explained the logic well.

gwassenaar  swuehl‌ sunindia‌ kush141087‌

‌

[sunny_talwar]

This is needed in the script or the front end of the application?

[#3]

Hi sunny,

I need this in script

[swuehl · Accepted solution]

Maybe like

INPUT:
LOAD ID,
    Seq,
    Category,
    Amount
FROM
[https://community.qlik.com/thread/213114]
(html, codepage is 1252, embedded labels, table is @1);


LOAD *,
  Rangesum(Cumulative, Peek(Tmp1)) as Tmp1;
LOAD ID,
    Seq,
    Category,
    Amount,
    If(ID <> previous(ID) or Category=Previous(Category), Amount, Rangesum(Peek(Tmp1),-Amount)) as Cumulative
Resident INPUT
ORDER BY ID, Seq;

DROP TABLE INPUT;

edit: there are some minor differences in the last few lines, but I think the calculations are correct:

ID	Seq	Category	Amount	Cumulative	Tmp1
1	1	a	100	100	100
1	2	a	200	200	300
1	3	a	300	300	600
1	4	b	200	400	1000
1	5	c	150	850	1850
2	6	c	300	300	2150
2	7	a	100	2050	4200
2	8	b	100	4100	8300
2	9	b	300	300	8600
2	10	a	175	8425	17025

[swuehl]

I am little bit unsure about your logic when ID change, so instead

  If(ID <> previous(ID) or Category=Previous(Category), ..

you might want a different logic (I believe my current logic is not correct)? Do you need to look at ID at all?

  If(Recno() = 1 or Category=Previous(Category), ...

[sunny_talwar]

So Stefan, preceding load is the key here right, there no way to have accomplished this without the preceding load or do you think it is still possible?

[swuehl]

Well, I think it should be possible to get everything in one load, by replacing the field reference with the full expression.

.

[sunny_talwar]

Something like this?

INPUT: 

LOAD ID, 

    Seq, 

    Category, 

    Amount 

FROM 

[https://community.qlik.com/thread/213114] 

(html, codepage is 1252, embedded labels, table is @1); 

LOAD *, 

  as Tmp1; 

LOAD ID, 

    Seq, 

    Category, 

    Amount, 

    If(ID <> previous(ID) or Category=Previous(Category), Amount, Rangesum(Peek(Rangesum(Cumulative, Peek(Tmp1))),-Amount)) as Cumulative 

Resident INPUT 

ORDER BY ID, Seq; 

DROP TABLE INPUT; 

Peek allows for using functions such as RangeSum and another Peek within it?

[sunny_talwar]

But I guess this won't work because we don't have Tmp1 available within the inner peek. And when I tried this:

INPUT:

LOAD ID,

     Seq,

     Category,

     Amount

FROM

[https://community.qlik.com/thread/213114]

(html, codepage is 1252, embedded labels, table is @1);

//LOAD *,

//  Rangesum(Cumulative, Peek(Tmp1)) as Tmp1;

LOAD ID,

     Seq,

     Category,

     Amount,

    Rangesum(Cumulative, Peek(Tmp1)) as Tmp1,

     If(ID <> previous(ID) or Category=Previous(Category), Amount, Rangesum(Peek(Tmp1),-Amount)) as Cumulative

Resident INPUT

ORDER BY ID, Seq;

DROP TABLE INPUT;

Again it throw me an error that Cumulative isn't there. So I guess cross referencing seems only possible with a preceding load here.

[swuehl]

Well, I was considering replacing the preceding load, not getting everything into one field expression:

LOAD ID,

     Seq,

     Category,

     Amount,

     If(Recno()=1 or Category=Previous(Category), Amount, Rangesum(Peek(Tmp1),-Amount)) as Cumulative,

     Rangesum( If(Recno()=1 or Category=Previous(Category), Amount, Rangesum(Peek(Tmp1),-Amount)), Peek(Tmp1)) as Tmp1

Resident INPUT

ORDER BY ID, Seq;