Total function that works with filters

[pascaldijkshoor]

Hi Guys,

I am currently using the following formula to divide shipment revenue on assets:

sum (aggr (sum (Shipment_Productivity)/sum (total <Shipment_Number_Linked> Shipment_Productivity)* sum ({$<DEVIDED={'Y'}>}AMOUNT),Shipment_Number_Linked,Asset_Number,Key))

I just found out that when I use a filter that's asset number related, the result of the formula is not correct anymore. I will explain this with the following example:

I have one shipment which is transported with 2 assets. The part of the revenue that every asset gets is dependent on the productivity of that asset (simple example: 1 asset drove 40km with the shipment, the other 60km, so the division of that shipment's revenue is 40/60). So first the shipment productivity of the asset is calculated, and then divided by the total shipment productivity of both assets. After this the revenue of the shipment can fairly be divided on both assets.

But when only 1 of the assets is selected, the total shipment productivity is now the total shipment productivity of the selected asset, and not of both assets anymore. This means the selected asset will get 100% of the shipment's revenue.

Is it possible to solve this, so I can select a group of assets with the correct outcome of the formula.

Thanks in advance

[ogautier62]

Hi

maybe you can add set analysis :

in sum(total <   >  ship_prod

add a set analysis like {$<Asset_Number=>}

this keep the selection, but remove selection on field asset_number

regards

[sunny_talwar · Accepted solution]

Try this by ignoring Asset_Number selection in the denominator of your expression which will insure that any selection in Asset_Number doesn't change the number in the denominator.

Sum(Aggr(Sum(Shipment_Productivity)/Sum ({<Asset_Number>} TOTAL <Shipment_Number_Linked> Shipment_Productivity) * Sum {$<DEVIDED = {'Y'}>} AMOUNT), Shipment_Number_Linked, Asset_Number, Key))

or ignore all selections

Sum(Aggr(Sum(Shipment_Productivity)/Sum ({1} TOTAL <Shipment_Number_Linked> Shipment_Productivity) * Sum {$<DEVIDED = {'Y'}>} AMOUNT), Shipment_Number_Linked, Asset_Number, Key))

[pascaldijkshoor]

Thanks ,the ignore all selections is really useful!