I am trying to do a seemingly simple thing. I thought I could use a result-set approach but it did not work.
So, here is the task: group averages of numbers based on a category. An aggregate insight does that well. The problem is I want to put in a formula to calculate the difference between the averages.
The result is a transposed set with a difference column:
Any and all suggestions are appreciated.
Solved! Go to Solution.
You can use a formula columns ( e.g. avg(case(cat=1,value,0 )) and avg(case(cat=2,value,0 ))
avg(case(cat=2,value,0 )) - avg(case(cat=1,value,0 ))
Although frankly doing that per insight is a pain, so if you wanted to build in a schema for easy re-use that would be one way to go.
Another would be to build it as a MV ( or derived table, or Incorta Analytic table ) which would work best if the number of categories isn't fixed.
I feel your pain! Incorta *should* allow you to use formula columns in other formula columns and handle this internally. It doesn't, so we have to 😉
When I've had issues w/ this one of the things which has been confusing is the order of operations w/in nested parens. Instead of " ( <formula> ) - ( <formula2> )
I've tried different sytnax:
(( <formula> - (( <formula2> ))
( (( <formula> - (( <formula2> )) )
And since "<formula>" can be really complex and use a lot of parens itself it can get confusing really quickly. I found myself working in Notepad++ and doing a lot of CTRL-C/CTRL-V between it and the expression editor.
I'll wind up w/ an insight w/ 10+ formula columns as I move forward step-by-step towards my final logic ( shameless plug: good case for being able to hide columns: https://community.incorta.com/t5/product-ideas/hide-show-columns-in-a-dashboard/idi-p/651 )
Candidly, I've done the above and it's a long, arduous process, so I've also taken the route of doing the calcs in a materialized view which is somewhat limited in that you need define static grouping ( e.g. by week or month ) which doesn't roll up well when you want to average something as in your example -- avg of weeks 1 - 4 does not necessarily equal the avg for the month.
Having written this much, if someone wants to swoop in with an easy answer I'll celebrate with you! 😉