topic Re: What is a simple Excel reference is hard in Incorta? in Dashboards & Analytics Discussions

What is a simple Excel reference is hard in Incorta?

marcpaige — Wed, 26 Oct 2022 21:28:42 GMT

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.

Re: What is a simple Excel reference is hard in Incorta?

RADSr — Thu, 27 Oct 2022 03:44:39 GMT

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.

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Thu, 27 Oct 2022 14:18:26 GMT

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.

Re: What is a simple Excel reference is hard in Incorta?

RADSr — Thu, 27 Oct 2022 14:35:03 GMT

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

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Thu, 27 Oct 2022 17:40:39 GMT

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.

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Fri, 28 Oct 2022 14:12:19 GMT

To round out the solution: I also had to use an Incorta Analyzer table to calculate the averages and have access to them from the KPI insight. The MV wouldn't allow me to do the calculations in SQL.

Re: What is a simple Excel reference is hard in Incorta?

ZachBreimayer — Fri, 28 Oct 2022 20:19:15 GMT

Here's how this can be solved using an MV with Spark SQL:

I'm using an 'AdventureWorks' database, if your not familiar its simply a sample database often used for testing for a retailer that has tables such as OrderDetail, OrderHeader, Products, ProductSubcategory, ProductCategory etc...

In the first screenshot you can see I have a query that joins a few tables to create a result set in the same format you have with Categories & AvgLineTotal. This will become an inner query, but I'm applying the avg here to improve query performance(reduce as early as possible)

In the screen shot below you can see the result set of the query:

Next, lets use a Pivot function in an outer query, youll notice an avg is applied in the pivot function as its required to apply an aggregate function with pivot, however this does not effect the result in this case. Also to note, most modern versions of sql support this now, so we are going to use this to swap the categories into fields as shown below:

After running this we can now see the result set getting closer to our desired outcome:

And finally, we can perform any function/mathematical operations against those category fields, in our case I chose 'Bikes-Clothing AS Bike_Clothing_Diff' in the outer query as shown below and we can see our difference:

SELECT *, Bikes-Clothing AS Bike_Clothing_Diff FROM( SELECT pc.categoryname AS Category, avg(sod.linetotal) AS AvgLineTotal FROM OnlineStore2.salesorderdetail sod Left Join OnlineStore2.product p ON sod.productid = p.productid Left Join OnlineStore2.productsubcategory ps ON p.productsubcategoryid = ps.productsubcategoryid Left Join OnlineStore2.productcategory pc ON ps.productcategoryid = pc.productcategoryid GROUP BY pc.categoryname ) a PIVOT(avg(AvgLineTotal) FOR Category IN('Bikes','Clothing','Accessories','Components'))

Hopefully this gives you an additional option, please let us know if you have any further questions or if you need any further detail anywhere in this process.

Regards,

Zach, Solutions Engineer @ Incorta

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Fri, 28 Oct 2022 20:41:20 GMT

Thank you, Zach. This is very helpful.

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Fri, 04 Nov 2022 19:06:47 GMT

I have a follow-up for you Zach. @ZachBreimayer: How would the SQL change for multiple aggregate columns?

Re: What is a simple Excel reference is hard in Incorta?

ZachBreimayer — Fri, 04 Nov 2022 21:20:26 GMT

Hi Marc,

Here is an example I have extended upon the previous. In the first image you can see that within the pivot function after the 'PIVOT(avg(AvgLineTotal' you can then simply add a comma and add the next aggregate 'SUM(OrderQTYTotal)' as shown in the first image below.

Doing this will create a field for each combination of aggregates and categories as shown in the next image.

SELECT *, Bikes_AvgLineTotal-Clothing_AvgLineTotal AS Bike_Clothing_AvgLineTotal_Diff, Bikes_OrderQTYTotal-Clothing_OrderQTYTotal AS Bike_Clothing_OrderQTYTotal_Diff FROM( SELECT pc.categoryname AS Category, avg(sod.linetotal) AS AvgLineTotal, sum(OrderQTY) AS OrderQTYTotal FROM OnlineStore2.salesorderdetail sod Left Join OnlineStore2.product p ON sod.productid = p.productid Left Join OnlineStore2.productsubcategory ps ON p.productsubcategoryid = ps.productsubcategoryid Left Join OnlineStore2.productcategory pc ON ps.productcategoryid = pc.productcategoryid GROUP BY pc.categoryname ) a PIVOT(avg(AvgLineTotal) AS AvgLineTotal, SUM(OrderQTYTotal) AS OrderQTYTotal FOR Category IN('Bikes','Clothing','Accessories','Components'))

Appreciate the follow up, always happy to help so let us know if you have any more questions!

Zach, Solutions Engineer @ Incorta

Re: What is a simple Excel reference is hard in Incorta?

marcpaige — Fri, 04 Nov 2022 21:35:35 GMT

Thanks again, Zach. This makes perfect sense.