FIXED () in Magic ETL?

verytiredgirl

'MTD'
/


SUM(

CASE

WHEN Class 3 = 'Sales'

THEN Amount

ELSE 0

END

)

FIXED ()

I have this BeastMode called 'MTD %' that calculate the % of each GL Account based on Sales (so Sales = 100%)And it was working correctly with different branches. Just wondering if I can create this in ETL?

ColemenWilson

Fixed functions only work in beast modes. To do this in ETL you will need to add a constant if one doesn't already exist, this will be needed for a Group By to sum up Amount. This step replicates the first part of the fixed function by getting you a total amount for sales for your grouping. The next step is to join the grouped data back to the rest of your data. Then you can compare the MTD to the total summed amount.

rco

There is no "FIXED()" in Magic ETL, but you can replicate its behavior with a combination of Group By and Join. In this case, it you'll want to configure a Group By like this:

And the subsequent Join like this:

And finally an Add Formula like this:

Arranged like this:

ColemenWilson

Fixed functions only work in beast modes. To do this in ETL you will need to add a constant if one doesn't already exist, this will be needed for a Group By to sum up Amount. This step replicates the first part of the fixed function by getting you a total amount for sales for your grouping. The next step is to join the grouped data back to the rest of your data. Then you can compare the MTD to the total summed amount.

rco

There is no "FIXED()" in Magic ETL, but you can replicate its behavior with a combination of Group By and Join. In this case, it you'll want to configure a Group By like this:

And the subsequent Join like this:

And finally an Add Formula like this:

Arranged like this:

verytiredgirl

@rco thanks for this, but might I ask why it's a cross join?
because my end goal is to have a Pivot Table that has column: MTD, MTD %. With the Cross Join, the MTD Amount will be inflated in the Pivot Table

rco

The Group By emits just one row, the sum for the whole table. The cross join just joins that single sum value to the original table; the final row count matches the original row count.