Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Wondering if something is possible, to help explain I have set iut an example below.
Say I have a straight table with 4 fields:
year,
quarter,
month,
quantity,
I would like to add a 5th field which is an aggregate function but only grouped by the year, i.e. I want to add quanity_year which is the sum of quantity for the year. Can I do this using an expression?
I have an example below:
table
year quarter month quantity year_quanity
2011 1 1 3 14
2011 2 . 5 14
2011 3 . 6 14
2012 1 . 8
Any suggestions? Thanks
Regards,
Revlin
SUM(TOTAL <year> quantity)
Hi,
you can calculate year_quantity with a group by.
QuantityByYear:
LOAD
year,
Sum(quantity) as year_quantity
Resident YourTable
Group By year;
If you want year_quantity field in YourTable just jon YourTable and QuantityByYear.
Left join (YourTable)
Load *, 'foo' as foo
Resident QuantityByYear;
Drop Field foo;
Regards.
Hi
Thanks for your reply. I understand that this can be achived as a preprocessing stage via say sql or via the load script as you have shown. However, I wanted to get more familier with the expressions and set analysis side of Qlikview. Is this possible to ahcive via an expression?
Regards,
Revlin
SUM(TOTAL <year> quantity)
Perfect - thats exactly what I was looking for! Thanks a lot it works