Jump to content
We've recently updated our Privacy Statement, available here. ×

Aggregated data using the keyword THEN with more than one column


Erick Perez

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...