Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
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
Partner

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)))

 

View solution in original post

wttaryde
Contributor III
Contributor III

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