Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
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.
Hi Wttaryde,
You can use the above function for this.
Above(TOTAL [YourFormula]) - [YourFormula]
Jordy
Climber
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)))
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.