Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Taking the Sum of an already summed expression

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

data_guru_001

Contributor III

2019-04-05
08:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Taking the Sum of an already summed expression

Hi,

I am trying to take the sum of two expressions being multipled against each other, but one of the expressions is a rangesum, not allowing me to do so. Please see the attached screenshot for an example look of what I am trying to do. I am also trying to avoid doing the rangesum in the load script.

I want to multiply the below two expressions against each other, **then** **after**, take the sum of it.

*Expression 1: Calculates the rolling 4 wk sum of my original expression: Sum(Qty)*

**=Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)**

**, 0, 4)), Group, YYYYWK))**

*Expression 2: Is just the Amt. *

**=Sum(Amt)**

So, what I need to do is:

**SUM(**

**Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)**

**, 0, 4)), Group, YYYYWK))**

*****

**Amt**

**)**

I think that because in my first expression, I am already taking the rangesum of my original expression, I cannot take the sum of the whole formula again.

Note: {< YYYYQ =, YYYYWK = >} is there just to say that upon making selections on these fields, do not change values. Can be disregarded on functionality, just needs to be included in syntax.

Any help would be greatly appreciated!

Regards

761 Views

1 Solution

Accepted Solutions

stevedark

Partner Ambassador/MVP

2019-04-06
05:09 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

If you ever find yourself wanting to do one aggregation inside another you need an AGGR. This effectively builds a temporary table where you can get the results of the inner aggregation at every dimension defined by the AGGR parameters.

In your case you will have an AGGR within your AGGR, which may cause weirdness, but give this a try:

SUM(

aggr(

Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)

, 0, 4)), Group, YYYYWK))

*

Amt,

Group,Person,YYYYWK)

)

Note that the fields listed as AGGR parameters for the outer parameters are taken from your screengrab, as these define the granularity you want the inner expression done to.

I think what you want to do can be simplified, as you do not want to see the table by week, and the rolling total can just be rolled up, effectively done on two rows. The code would then be:

sum(aggr(avg(Qty*Amt), Group, Person))

I may be missing something about what you are looking to achieve though?

Hope that makes sense.

Steve

If you ever find yourself wanting to do one aggregation inside another you need an AGGR. This effectively builds a temporary table where you can get the results of the inner aggregation at every dimension defined by the AGGR parameters.

In your case you will have an AGGR within your AGGR, which may cause weirdness, but give this a try:

SUM(

aggr(

Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)

, 0, 4)), Group, YYYYWK))

*

Amt,

Group,Person,YYYYWK)

)

Note that the fields listed as AGGR parameters for the outer parameters are taken from your screengrab, as these define the granularity you want the inner expression done to.

I think what you want to do can be simplified, as you do not want to see the table by week, and the rolling total can just be rolled up, effectively done on two rows. The code would then be:

sum(aggr(avg(Qty*Amt), Group, Person))

I may be missing something about what you are looking to achieve though?

Hope that makes sense.

Steve

738 Views

3 Replies

stevedark

Partner Ambassador/MVP

2019-04-06
05:09 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

If you ever find yourself wanting to do one aggregation inside another you need an AGGR. This effectively builds a temporary table where you can get the results of the inner aggregation at every dimension defined by the AGGR parameters.

In your case you will have an AGGR within your AGGR, which may cause weirdness, but give this a try:

SUM(

aggr(

Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)

, 0, 4)), Group, YYYYWK))

*

Amt,

Group,Person,YYYYWK)

)

Note that the fields listed as AGGR parameters for the outer parameters are taken from your screengrab, as these define the granularity you want the inner expression done to.

I think what you want to do can be simplified, as you do not want to see the table by week, and the rolling total can just be rolled up, effectively done on two rows. The code would then be:

sum(aggr(avg(Qty*Amt), Group, Person))

I may be missing something about what you are looking to achieve though?

Hope that makes sense.

Steve

739 Views

data_guru_001

Contributor III

2019-04-06
09:50 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Steve,

This expression works perfectly:

SUM(

aggr(

Avg(Aggr(RangeSum(Above(Sum({< YYYYQ =, YYYYWK = >} Qty)

, 0, 4)), Group, YYYYWK))

*

Amt,

Group,Person,YYYYWK)

)

Thank you so much for the help on this, it really helped me out.

stevedark

Partner Ambassador/MVP

2019-04-07
02:23 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That's great to hear, thanks for letting me know.

Steve

716 Views