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

Aggr Nested in Set Analysis

A simple example but I am stuck ...

Previous Year

=Sum({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>}aggr(ApprovedPC, ActivityID)) is not returning any results

Selected Year

=Sum({$<ActivityStartYear={$(=Only(ActivityStartYear))}>}aggr(ApprovedPC, ActivityID)) ... is ... the difference being the -1

Basically I am trying to do a simple year on year table but need to aggregate the results by ActivityID before summing the value.

Could anyone explain why it fails on previous year but selected year is OK?

Thanks

Lee

1 Solution

Accepted Solutions
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

I think the right use of Aggr is like this:

Sum({Set} (AGGR( Sum( {Set} Fieldname), Dim1, Dim2))

I miss in your expression the second part inside the Aggr-function: Sum( {Set} Fieldname) or Avg ...

View solution in original post

20 Replies
isaiah82
Creator III
Creator III

Hi Lee - try sticking the expressions to determine your year into a variable and then substitute into the set analysis expression.

Let me know if this doesn't make any sense.

-Isaiah

Not applicable
Author

Put your Previous Year into an expression, but don't give it a label. When the chart is rendered, the label will be the Set Analysis expression with the dollar sign expansion evaluated. I don't see an obvious reason as why the Selected Year would work, but the previous would not. You are making a selection when using the previous year expression, correct?

I would also try to put the Aggr() outside of the Sum(). I don't know if it matters in this instance though. It may be worth a try.

Not applicable
Author

Hi,

@Isaiah - Using a variable for both previous and selected year has got them both calculating but the -1 is being ignore, so they are now both calculating the selected year ... it now looks like this ...

=Sum({$<vPreviousYear={$(=Only(vPreviousYear))}>}aggr(ApprovedPC, ActivityID))

@NMiller - thanks for idea ... the AGGR is pretty crucial to getting the value correct ... the selected year is correct it just won't calculate the year ...

However - I have a dynamic label on the previous year column and that is working fine ...

='Previous Year - ' & $(vPreviousYear)

So it is something to do with calculating the previous year and the AGGR ... or so it would seem ... might be off course.

Not applicable
Author

I'm not sure where the Set Analysis is being calculated. When the Aggr() is on the outside, it means Sum according to this logic, while aggregating by Activity ID. When the Aggr is inside, it is Aggregating by Activity ID and then summing according to the logic.

The problem seems to come from your -1, with all else being equal. You need to make sure your dollar sign expansion is returning a value for the previous year and then look into the Aggr().

Not applicable
Author

Ah ... I think the AGGR() is causing the SET to not evaluate correctly ... because hardcoding the different years in is not making a difference ...

Not too sure how to get round that ...

pover
Luminary Alumni
Luminary Alumni

Lee,

When you do a aggr(ApprovedPC,ActivityID) it is like doing a aggr(only(ApprovedPC,ActivityID). I'm not sure why you would do that because it returns null if a ActivityID has more than 1 unique value, but maybe that is necessary in your case, so try the following:

Sum(aggr(only({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>} ApprovedPC), ActivityID))

Regards.

Not applicable
Author

In that case, I'll suggest changing the order of functions.

aggr(Sum({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>} ApprovedPC), ActivityID)


Not applicable
Author

Hi Karl,

Thanks for the pointer ... I always viewed AGGR() as similar to Group By in SQL ... such that if your data is a flat file you could return the unique value for something by its dimension ... in my case ... ApprovedPC will be unique per ActivityID but due to other related data there are multiple records in the table.

I am afraid however that the syntax just returns 0 ... in other scenarios an expression such as sum(aggr(ApprovedPC, ActivityID)) is working fine and calculating correctly so I think I have the AGGR concept right for the data structure ... just can't get it working in tandem with the sets.

Thanks

Lee

Not applicable
Author

@NMiller - nope ... just returns a missing value ... does not evaluate.