topic Re: Cummulative Calculation based on condition in QlikView

Cummulative Calculation based on condition

Wed, 13 Apr 2016 11:43:23 GMT

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‌

‌

Re: Cummulative Calculation based on condition

sunny_talwar — Wed, 13 Apr 2016 12:19:25 GMT

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

Re: Cummulative Calculation based on condition

Wed, 13 Apr 2016 12:36:59 GMT

Hi sunny,

I need this in script

Re: Cummulative Calculation based on condition

swuehl — Wed, 13 Apr 2016 12:51:23 GMT

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

Re: Cummulative Calculation based on condition

swuehl — Wed, 13 Apr 2016 13:05:10 GMT

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), ...

Re: Cummulative Calculation based on condition

sunny_talwar — Wed, 13 Apr 2016 13:07:59 GMT

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?

Re: Cummulative Calculation based on condition

swuehl — Wed, 13 Apr 2016 13:11:25 GMT

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

Re: Cummulative Calculation based on condition

sunny_talwar — Wed, 13 Apr 2016 13:14:06 GMT

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?

Re: Cummulative Calculation based on condition

sunny_talwar — Wed, 13 Apr 2016 13:17:09 GMT

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.

Re: Cummulative Calculation based on condition

swuehl — Wed, 13 Apr 2016 13:24:13 GMT

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;

Re: Cummulative Calculation based on condition

perumal_41 — Wed, 13 Apr 2016 13:51:56 GMT

alternative solution

Temp1:
LOAD * INLINE [
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
];

Temp2:
Load * ,if(RowNo()=1,Amount,if (Category=Peek(Category), Peek(Cum)+Amount,Peek(Cum)+Peek(Cum)-Amount)) as Cum,if(RowNo()<>1 and Category <> peek(Category), Amount,0) as Deu Resident Temp1;

Load *,if(RowNo()=1,Cum,if(Category<>Peek(Category),Cum-Peek(Cum),Amount)) as Cum1 Resident Temp2;

DROP Table Temp1,Temp2;

Re: Cummulative Calculation based on condition

Wed, 13 Apr 2016 14:44:29 GMT

thanks all.. and specially qlik superhero swuehl‌