topic Re: Calculating Cumulative Sums and Incremental values together in QlikView

Calculating Cumulative Sums and Incremental values together

Anonymous — Wed, 08 Jun 2016 08:38:34 GMT

Dear experts,

I am quite new to Qlikview and need help of experienced users about my below issue.

I have a table like that

Item	File	Year	Quarter	Amount
A	100	2012	1	50
B	101	2012	1	100
A	102	2012	2	60
B	103	2012	2	250
A	104	2012	3	55
B	105	2012	3	300
A	106	2012	4	65
B	107	2012	4	400

And I want to calculate the total result with cumulative sums of item A values and incremental value of Item B as per and of quarter of the year.

Year	2012	2012	2012	2012
Quarter	1	2	3	4
Item A	50	60	55	65
Item B	100	250	300	400
Result	150	410	705	1115
Formula	Value of 2012 A1 + sum(B1+B2)	Value of A2 + sum(B1+B2+B3)	Value of A3 + sum(B1+B2+B3+B4)	Value of A4 + sum(B1+B2+B3+B4)

I am fine with solutions working over both in load script or as expression. Can someone help me out on this one?

Thank you very much in advance!

Re: Calculating Cumulative Sums and Incremental values together

sunny_talwar — Wed, 08 Jun 2016 09:22:23 GMT

Check out the attached

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 09:33:02 GMT

also try this

Data:

LOAD Item,

Year,

File,

Quarter,

Amount

FROM table;

New:

noconcatenate

LOAD *,

if(rowno()=1 or Year<>Previous(Year) or Quarter<>Previous(Quarter),0,

if(Year=previuos(Year) and Quarter=Previous(Quarter),

rangesum(Amount,previous(Amount)),0)) as Accum_AB_1

resident Data

order by Year,File;

left join(New)

LOAD Year,

Quarter,

Sum(Accum_AB_1) as Accum_AB

Resident New

Group by Year,Quarter;

drop field Accum_AB_1;

left join(New)

LOAD Year,

Quarter,

if(rowno()=1,Amount,rangesum(Amount,peek('Accum_B'))) as Accum_B

resident New

where Item='B'

order by Year,Quarter;

drop table Data;

Create Pivot table

Dimension :

Item

Year

Quarter

Expression:

1) Amount-

Sum(Amount)

2) Result-

Sum(distinct Accum_AB)+Sum(distinct Accum_B)

Drag and drop the Quarter dimension horizontally

Re: Calculating Cumulative Sums and Incremental values together

Anonymous — Wed, 08 Jun 2016 09:58:38 GMT

Thanks guys both your solutions are perfectly working for my requirement.

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 10:18:49 GMT

Not a problem

Re: Calculating Cumulative Sums and Incremental values together

sunny_talwar — Wed, 08 Jun 2016 10:24:17 GMT

No offense to kushal‌, but you seem to like longer script compared to a smaller script:

Table:

LOAD * INLINE [

Item, File, Year, Quarter, Amount

A, 100, 2012, 1, 50

B, 101, 2012, 1, 100

A, 102, 2012, 2, 60

B, 103, 2012, 2, 250

A, 104, 2012, 3, 55

B, 105, 2012, 3, 300

A, 106, 2012, 4, 65

B, 107, 2012, 4, 400

];

FinalTable:

LOAD *,

If(Year = Previous(Year) and Item = Previous(Item) and Item = 'B', RangeSum(Amount, Peek('New_Amount')), Amount) as New_Amount

Resident Table

Order By Year, Item, Quarter;

DROP Table Table;

Update: Expression used for getting the desired output:

Sum({<Item = {'A'}>}Amount) + Sum({<Item = {'B'}>}New_Amount)

I would not have thought that you would not mark my response as correct, but we both are glad that you got what you were looking for.

Best,

Sunny

Re: Calculating Cumulative Sums and Incremental values together

Anonymous — Wed, 08 Jun 2016 10:33:43 GMT

Dear Sunny,

Actually I am using a personal edition and could not see your script and since it was a bit urgent I had to use which one is more conveniently available. Please be sure that your help and effort also appreciated.

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 11:03:14 GMT

Yeah, because I don't want to perform any set analysis or any expression in front end.(Just want to have simple expression) i.e why i used this script.

Re: Calculating Cumulative Sums and Incremental values together

sunny_talwar — Wed, 08 Jun 2016 11:42:34 GMT

Personally, I would not want to complicate my script in order to avoid set analysis on the front end. But I guess OP disagrees with me. Keeping this discussion aside, what is your opinion on this?

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 12:02:59 GMT

I think it's not too complicated I guess I always try to do most of the calculation in script keeping in mind that not to increase loading time and memory usage.

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 12:04:52 GMT

Hi egemenekinci, further if you get the time then try to implement what sunny has suggested and if you find it more easy and efficient then you can any time mark his answer correct and mine as helpful.

Re: Calculating Cumulative Sums and Incremental values together

sunny_talwar — Wed, 08 Jun 2016 12:32:14 GMT

I agree its not complicated, but joins can sometime be painfully slow. So my question is, do we think that Joins are better than using Set Analysis?

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Wed, 08 Jun 2016 12:46:14 GMT

If joins are not performed on large data set & also joins are not performed combined key then join is always efficient as it brings all the relevant dimensions & measures into single table, but at the same time it increases the application size as underlying symbol table size increases. So that all depends on amount of data and requirement.

Re: Calculating Cumulative Sums and Incremental values together

Anonymous — Thu, 09 Jun 2016 04:32:32 GMT

I have working with a data around 500K records and actually your solution through join is not so effective... stalwar1‌,'s solution works more quicker as expected.

Re: Calculating Cumulative Sums and Incremental values together

Kushal_Chawda — Thu, 09 Jun 2016 06:35:45 GMT

egemenekinci‌ you can also mark my answer as helpful if so