What is a simple Excel reference is hard in Incorta?

[marcpaige]

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.

Data:

Category	Average A
Cat1	10
Cat2	5

The result is a transposed set with a difference column:

Metric	Cat1	Cat2	Difference
Average A	10	5	5

Any and all suggestions are appreciated.

[RADSr]

You can use a formula columns ( e.g. avg(case(cat=1,value,0 ))   and  avg(case(cat=2,value,0 ))  

then

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.

[marcpaige]

The formulas give me proper values but the difference still does not work. I have tried to build an MV but come up with the same problem. Please elaborate on your thought there.

[RADSr]

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!  😉   

[marcpaige]

I found an alternative way to show the data! I was playing around and tried using KPIs and the formulas worked! I will show the variances in a KPI insight and the transposed table next to it. Looks great and it highlights the variances with conditional formatting.