topic Re: Sum only grouped by one field? in QlikView

Sum only grouped by one field?

Thu, 14 Jun 2012 15:59:18 GMT

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

Re: Sum only grouped by one field?

Thu, 14 Jun 2012 18:45:01 GMT

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.

Re: Sum only grouped by one field?

Fri, 15 Jun 2012 08:17:32 GMT

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

Re: Sum only grouped by one field?

Fri, 15 Jun 2012 08:26:29 GMT

SUM(TOTAL <year> quantity)

Re: Sum only grouped by one field?

Fri, 15 Jun 2012 08:37:41 GMT

Perfect - thats exactly what I was looking for! Thanks a lot it works