Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.