Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum only grouped by one field?

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

1 Solution

Accepted Solutions
Not applicable
Author

SUM(TOTAL <year> quantity)

View solution in original post

4 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

SUM(TOTAL <year> quantity)

Not applicable
Author

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