Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Sum only grouped by one field? (Part 2)

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

6 Replies
MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

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.

MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

Hi,

Pls. have a look at the attached file.

I only hid the dimensions from presentation tab.

Best regards,

http://quickdevtips.blogspot.com/

Not applicable

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

Hi Jonathan

ok - please see attached Qlikview file.

Rgds

Revlin

Not applicable

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

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

Community Browser