topic Set Analysis Question in QlikView

Set Analysis Question

Wed, 01 Feb 2012 16:27:39 GMT

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

Set Analysis Question

Fri, 03 Feb 2012 08:25:05 GMT

Can anyone help me with this?

Thanks

Kev.

Set Analysis Question

Fri, 03 Feb 2012 09:34:43 GMT

Hi Kevin,

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

Why do you want to do it?

Regards,

Björn

Set Analysis Question

Fri, 03 Feb 2012 09:41:14 GMT

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

Re: Set Analysis Question

Fri, 03 Feb 2012 09:55:12 GMT

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

Set Analysis Question

Mon, 06 Feb 2012 16:20:18 GMT

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

Regards

Kev.