Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Check out the attached
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
Thanks guys both your solutions are perfectly working for my requirement.
Not a problem
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
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.
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.
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?
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.