topic Re: Sum only grouped by one field? (Part 2) in QlikView

Sum only grouped by one field? (Part 2)

Fri, 15 Jun 2012 10:27:40 GMT

Hi,

I asked a question yesterday, which has been answered but I have an additional question that takes it that step further. I will repeat the first question then move on to my actual question:

First question:

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?

example data below (for illustrative purposes):

table

year quarter month quantity year_quanity

2011 1 1 3 14

2011 2 . 5 14

2011 3 . 6 14

2012 1 . 8

ANSWER: defining year_quantity as an expression defined as =sum(TOTAL <year> quantity). This works fine and achieve what I required as long as the year field is included in the straigh table.

Actual question:

Say I have the same data (as in table above) but this time I want to have a concatination of year and month as one field, then the sum of quantity. In other words, I want to remove the fields year, quarter, month and only have 3 fields, as below.

year_month quantity year_quanity

2011 1 3 14

2011 . 5 14

2011 . 6 14

2012 . 8

This however does not work as now the field year is not included in the straight table and the year_quantity expression stops working. The expression ends up giving me a total quanity over the whole dataset and does not factor in year at all.

Any ideas on how I can get the sum of quantity grouped by year but without actually including the year in the straight table? I know I could do this as a preporcessing step (say via sql) or via the load script but I was hoping to do this via an expression.

Regards

Revlin

Re: Sum only grouped by one field? (Part 2)

jonathandienst — Fri, 15 Jun 2012 11:14:29 GMT

Try something like

Sum(Aggr(Sum(If(Left(year_month, 4) = year, quantity)), year, year_month))

(You might not need year_month as the second parameter to the Aggr)

If that does not work, I suggest that you post a sample so that we can see how to do this with your data model.

Regards

Jonathan

Re: Sum only grouped by one field? (Part 2)

Fri, 15 Jun 2012 12:24:05 GMT

Hi Jonathan

Sorry that expression does not work. Also, cannot attach a qliview file as I am using the tril/personal edition which means I can only use local files created on my own version of qlikview.

Re: Sum only grouped by one field? (Part 2)

jonathandienst — Sun, 17 Jun 2012 17:46:06 GMT

Revlin

Using personal edition does not stop you posting a qvw file - I will still be able to read it as I have licensed copy of QV (as will many others who may also be able to help you).

Once I see how to do what you are requesting, I can post the expression or script text here for you to try in your model.

Regards

Jonathan

Re: Sum only grouped by one field? (Part 2)

Sun, 17 Jun 2012 20:20:39 GMT

Hi,

Pls. have a look at the attached file.

I only hid the dimensions from presentation tab.

Best regards,

http://quickdevtips.blogspot.com/

Re: Sum only grouped by one field? (Part 2)

Mon, 18 Jun 2012 20:35:59 GMT

Hi Jonathan

ok - please see attached Qlikview file.

Rgds

Revlin

Re: Sum only grouped by one field? (Part 2)

Tue, 19 Jun 2012 16:06:46 GMT

Couldn't attach the file to the reply so had to attach it to the question above.