Calculating Cumulative Sums and Incremental values together

[Anonymous]

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!

[sunny_talwar · Accepted solution]

Check out the attached

[Kushal_Chawda]

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

[Anonymous]

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

[Kushal_Chawda]

Not a problem

[sunny_talwar]

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

[Anonymous]

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.

[Kushal_Chawda]

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.

[sunny_talwar]

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?

[Kushal_Chawda]

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.