Different Values for Measures Calculated in Front End vs Load Editor

bsullivan28 — Fri, 10 Dec 2021 21:09:00 GMT

Hey all

Need some help with calculations that I want to perform in the data load editor.

I have been working on a data model in which the raw data comes in as a short, wide table with the measures listed as column headers. I created PKs for the dimensions and the resulting table looks similar to this generic structure, NumLines also being a measure.

%contract_type, %plan_type, %Date, NumLines, Measure_1, Measure_2, Measure_3, etc.

Then I perform a CrossTable on this to get all measure names in the field 'Metric', and values in the field 'Value'.

%contract_type, %plan_type, %Date, Metric, Value

All of the Measure_# values need to be divide by a sum of NumLines.

On the front end, if i create a calculation that is: sum({$<Metric={'Measure_1'}>} Value)/sum({$<Metric={'lines_0'}>} Value)

I get the correct value that I am looking for. I would need to do this for all other measures as well.

My problem is when I am trying to move this calculation logic to the data load editor script. Instead of including NumLines in the same CrossTable, I loaded it into its own table where I did a Sum() and Group By with all PKs. Then doing an Inner Join I get a resulting table of:

%contract_type, %plan_type, %Date, NumLines, Metric, Value

I then tried to perform the Measure/NumLines calculation here but the values I am getting are highly inflated by millions. I have tried doing:

Value/NumLines as PerLine

sum(Value/NumLines) as PerLine

Then on the front end i would try to do sum(PerLine) or avg(PerLine) and was not getting correct numbers regardless.

I would like these calculations performed in the load script because only metrics on a 'per line' basis are looked at. I basically just need to recreate the working calculation I was using in the front end with set analysis.

Included screenshots of my data load editor. NumLines is lines_0 and usage is an example metric I am using to test.

topic Different Values for Measures Calculated in Front End vs Load Editor in Connectivity & Data Prep

Different Values for Measures Calculated in Front End vs Load Editor