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: 
wttaryde
Contributor III
Contributor III

KPI from Aggregation

I have used this expression to successfully produce my line chart:

Avg( Aggr((Sum({$<Question={"CT305A*"}>}Response)+Sum({$<Question={"CT305B*"}>}Response))
,CompName,Year)

On my line chart I have 7 years with values like: 2012 = 1.004, 2013 = 1.241 ... 2018 = 1.421

Now I need to calculate a delta comparing the last year value to the first year value: YearLast / (YearLast-YearFirst) or 1.421 / (1.421 - 1.004)

In my mind, I just need the aggregated value for the years and would modify my expression like this:

Avg( Aggr((Sum({$<Question={"CT305A*"}>}Response)+Sum({$<Question={"CT305B*"}>}Response))
,CompName,Year='2018')

but of course, that doesn't work.  I'm not sure where to put an IF statement or something else to get my average aggregated value but just for 2018.

1 Solution

Accepted Solutions
sunny_talwar

First of all, can you use this expression instead of the one you have currently?

Avg(Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year)

And to check Max year's value against the min year's value may be this...

1 - 
(Avg({<Year = {"$(=Max(Year))"}>}Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year))
/
Avg({<Year = {"$(=Min(Year))"}>}Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year)))

 

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

Can you share some sample data to look into. And you cannot give Year =2018 like that in Aggr function. You can use set modifier like Avg({<Year = {2018}>} Aggr(.....), field1, field2). But i doubt this would give what you want. A sample data will help with expected output. 

JordyWegman
Partner - Master
Partner - Master

Hi Wttaryde,

You can use the above function for this.

Above(TOTAL [YourFormula]) - [YourFormula]

Jordy

Climber 

Work smarter, not harder
sunny_talwar

First of all, can you use this expression instead of the one you have currently?

Avg(Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year)

And to check Max year's value against the min year's value may be this...

1 - 
(Avg({<Year = {"$(=Max(Year))"}>}Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year))
/
Avg({<Year = {"$(=Min(Year))"}>}Aggr(
    Sum({$<Question = {"CT305A*", "CT305B*"}>} Response)
, CompName, Year)))

 

wttaryde
Contributor III
Contributor III
Author

Thanks, Sunny, for cleaning up my calculation. That particular change is going to save me hours in the future.

Secondly, your calculation worked fine. I'll be using that a lot as well.

Thanks for your help.

 

Screen Shot 2019-11-05 at 9.30.07 AM.png