Expression to shared costs

[silvia_assuncao]

Hi,

I'm  trying to create an expression dividing the total cost per number of resources. The problem on doing this is becase there is no link between Unique Resource ID Field.

I'm attaching a spreadsheet with the expression result I'd like to have.

could you help me?

[sunny_talwar]

May be this:

=If([Acconts Qt] > 0,

Sum(TOTAL Aggr(

( Sum(If([Adjust Type]='C' and [Adjust Signal]='-',([Adjust Amount]*(-1)/100))))

  + Sum(If([Adjust Type]='C' and [Adjust Signal]='+',([Adjust Amount]/100)))

  +Sum(If([Discount Type]='C' and [Discount Signal]='-',([Discount Amount]*(-1))/100))

+ Sum(If([Plan Type]='C' and [Registry ID] = '60',[Plan Amount with Taxes])/100)

, [Unique Account ID], [Unique Resource ID]))/

Sum(TOTAL Aggr(Count(Distinct(if(left([Unique Resource ID],1)<>' ' and left([Unique Resource ID],1)<>'',[Unique Resource ID])))

, [Unique Account ID], [Unique Resource ID])))

[Gysbert_Wassenaar]

Try this expression:

rangesum(

  Sum({<[Adjust Type]={'C'}, [Adjust Signal]={'-'}>} total [Adjust Amount]*(-1))

  ,Sum({<[Adjust Type]={'C'}, [Adjust Signal]={'+'}>} total [Adjust Amount]) 

  ,Sum({<[Discount Type]={'C'}, [Discount Signal]={'-'}>} total [Discount Amount]*(-1))

  ,Sum({<[Plan Type]={'C'}, [Registry ID] = {'60'}>} total [Plan Amount with Taxes])

)

/ 100

/ sum(total aggr(Count({<[Unique Resource ID]={"=Len(Trim([Unique Resource ID]))>0"}>} Distinct [Unique Resource ID]), [Unique Account ID], [Unique Resource ID]))

To make things a bit easier you could create multiplier fields for the signal fields with values -1 and 1. That way you don't have to test the value of the xxx Signal fields and manually add a multiplier. You can simply multiple with the multiplier field you created in the script.

[silvia_assuncao]

Hi Sunny, this solves 99%, but what could be done so that in the total value we would vale to real sum (40,833)?

Thanks a lot for your help

[sunny_talwar]

Try this:

=Sum(Aggr(If(Count(Distinct(if(left([Unique Resource ID],1)<>' ' and left([Unique Resource ID],1)<>'',[Unique Resource ID]))) > 0,

Sum(TOTAL Aggr(

( Sum(If([Adjust Type]='C' and [Adjust Signal]='-',([Adjust Amount]*(-1)/100))))

  + Sum(If([Adjust Type]='C' and [Adjust Signal]='+',([Adjust Amount]/100)))

  +Sum(If([Discount Type]='C' and [Discount Signal]='-',([Discount Amount]*(-1))/100))

+ Sum(If([Plan Type]='C' and [Registry ID] = '60',[Plan Amount with Taxes])/100)

, [Unique Account ID], [Unique Resource ID]))/

Sum(TOTAL Aggr(Count(Distinct(if(left([Unique Resource ID],1)<>' ' and left([Unique Resource ID],1)<>'',[Unique Resource ID])))

, [Unique Account ID], [Unique Resource ID]))), [Unique Account ID], [Unique Resource ID]))

[silvia_assuncao]

Yes!!!! Totally worked!! Thanks a lot!!!!!