Set Analysis Question

[#1]

How would I write the following as a set analysis -

Sum(if(BusinessTransactionTypeName='NB',(Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365))))

[#2]

Can anyone help me with this?

Thanks

Kev.

[#3]

Hi Kevin,

it is not possible to write your expression into a Set Analysis.

Why do you want to do it?

Regards,

Björn

[#4]

Hi Bjorn,

Reason is because I have a lot of IF statements in full formula (below) and I have associated this as being the cause of wait times in my document - someone suggested Set Analysis would speed up process - full formula =

Sum(if(BusinessTransactionTypeName='NB',(Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365))))

+Sum(if(BusinessTransactionTypeName='RENEWAL',(Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365))))

+Sum(if(BusinessTransactionTypeName='NTU',(Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365))))

+Sum ( if(BusinessTransactionTypeName='CANCEL',(Financials.IP/(AmendedEffectiveEndDate-AmendedEffectiveStartDate))*

if($(PictureDate)-AmendedEffectiveStartDate<0,0,if($(PictureDate)-AmendedEffectiveStartDate<AmendedEffectiveEndDate-AmendedEffectiveStartDate,$(PictureDate)-AmendedEffectiveStartDate,AmendedEffectiveEndDate-AmendedEffectiveStartDate))))

+Sum ( if(BusinessTransactionTypeName='MTA',(Financials.IP/(AmendedEffectiveEndDate-AmendedEffectiveStartDate))*

if($(PictureDate)-AmendedEffectiveStartDate<0,0,if($(PictureDate)-AmendedEffectiveStartDate<AmendedEffectiveEndDate-AmendedEffectiveStartDate,$(PictureDate)-AmendedEffectiveStartDate,AmendedEffectiveEndDate-AmendedEffectiveStartDate))))

[#5 · Accepted solution]

Ok I see,

this effects of course higher CPU usage.

You can try to combine the first three expressions:

Sum(if(Match(BusinessTransactionTypeName,'NB','NTU','RENEWAL')>0,(Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365))))

You can also try it like this (Set Expression instead of "MATCH"):

Sum(

     {< BusinessTransactionTypeName={'NB','NTU','RENEWAL'}>}

     (Financials.IP/365)*if($(PictureDate)-AmendedEffectiveStartDate<0,0,RangeMin($(PictureDate)-AmendedEffectiveStartDate,365)))

)

Regards,

Björn

[#6]

Thanks Bjorn, went for the Set Expression, speeded up the calculation significantly.

Regards

Kev.