Aggregated data using the keyword THEN with more than one column

[Erick Perez]

Hello,

Can you help me with this custom expression please, I am using on a cross table the aggregation function [% of Total] in Axis.Columns, and this percentage I need to operate at the Axis.Row level, but the THEN function does not allow me perform operations between the result and other columns.

Operar mas de una columna con funcion [% of Total]

For example:

[Column] - ([Column2] * [% of Total]) as [Purchase]

Sum([inventario])) - Max([CANTIDAD])*(Sum([Ventas]) THEN [Value] / Sum([Value]) OVER (All([Axis.Columns]))AS [Compra]

Attachment screenshot.

Thanks for your help and time.

Best regards,

Erick

[Khushboo Rabadia]

It would be helpful if you can provide sample dxp showing the issue and expected output screenshot

[Erick Perez]

Khushboo,I attached the screenshot of the expected result and the example of the database.Thank you very much for your help and time.Best regards,

Erick

[Erick Perez]

File.

[Khushboo Rabadia]

Hello Eric,

You do not need to include post-aggregation "THEN" in your calculation. You can create custom expression without that as well for (5) and (6) columns like below:

Sum([sALES]) / Sum([sALES]) OVER (All([Axis.Columns])) * Avg([MIN STOCK]) as [(5) Stock Ideal = (4)*(MIN STOCK)],

Sum([sTOCK]) - (Sum([sALES]) / Sum([sALES]) OVER (All([Axis.Columns])) * Avg([MIN STOCK])) AS [(6) Order = (1)-(5)]Above gives same results in the dxp you provided. Can you apply it to your real dataset to see it works as expected

[Erick Perez]

Hello Khushhboo,

 

I already did the test and it works correctly, thank you very much for your help and time.

 

Best regards.