topic Expression to shared costs in QlikView

Expression to shared costs

silvia_assuncao — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Expression to shared costs

sunny_talwar — Wed, 15 Jun 2016 18:07:06 GMT

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])))

Re: Expression to shared costs

Gysbert_Wassenaar — Wed, 15 Jun 2016 18:22:00 GMT

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.

Re: Expression to shared costs

silvia_assuncao — Wed, 15 Jun 2016 18:27:15 GMT

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

Re: Expression to shared costs

sunny_talwar — Wed, 15 Jun 2016 18:39:17 GMT

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]))

Re: Expression to shared costs

silvia_assuncao — Wed, 15 Jun 2016 19:24:23 GMT

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