4 Replies Latest reply: Jan 20, 2012 11:56 AM by kevinsmith

Capping a value

Hi All, I need some help with capping a number - I have the following formula

Sum ( if(BusinessTransactionTypeName='NB',(\$(PictureDate)-EffectiveStartDateTime)/365 ))

+Sum ( if(BusinessTransactionTypeName='RENEWAL',(\$(PictureDate)-EffectiveStartDateTime)/365 ))

-Sum ( if(BusinessTransactionTypeName='NTU',(\$(PictureDate)-EffectiveStartDateTime)/365 ))

-Sum ( if(BusinessTransactionTypeName='CANCEL',(\$(PictureDate)-EffectiveStartDateTime)/365 ))

However, if the picture date -effective date happens to be higher than 365 I need to cap at 365. I therefore assume I need to aggregate this and add a cap.

Thanks

KJS

• Capping a value

Sum ( if(BusinessTransactionTypeName='NB',(RangeMin(\$(PictureDate)-EffectiveStartDateTime,365))/365 ))

+Sum ( if(BusinessTransactionTypeName='RENEWAL',(RangeMin(\$(PictureDate)-EffectiveStartDateTime,365))/365 ))

-Sum ( if(BusinessTransactionTypeName='NTU',(RangeMin(\$(PictureDate)-EffectiveStartDateTime,365))/365 ))

-Sum ( if(BusinessTransactionTypeName='CANCEL',(RangeMin(\$(PictureDate)-EffectiveStartDateTime,365))/365 ))

Try using RangeMin() as edited above.

(You may also want to consider using RangeSum() and some set analysis instead of the IFs)

Hope this helps,

Jason

• Capping a value

Worked perfectly, thanks Jason, So how would I take this a step further and sum another column based on the above - eg.

Policy starts NB 1st Aug 2011 premium £1200

Policy CANCEL 1st Sept2011 premium -£1100

so if I looked at the position on 1st Sept I would see that I would have earned £100 (1200/12) if the record doesn't CANCEL on 1st sept and only top line existited on 1st Oct it would say £200 but because it has cancelled position at any date after 1st sept will only show £100

Only just started using Qlikview, will take a look at RangeSum & set analysis as am conscious if statememnts will slow process down.

• Capping a value

Sorry Kevin but I don't quite understand you added scenario.  Can you expand a little?

• Re: Capping a value

I will try!

I have the following which is a shortened version of my database. The formula you provided me with earlier calculates how long a policy has been running for at a particular time (picture date)-effectivestartdate time. I now need to work out the Financials.IP based on the picturedate-effectivestartdate

PolicyReference BusinessTransactionTypeName EffectiveStartDateTime EffectiveEndDateTime Financials.IP
017 NB 08/08/2011 07/08/2012 226.2000
017 CANCEL 21/09/2011 07/08/2012 -218.4100

The other formula isn't working either, it's because of the way I described it. Ultimately the NB can't go over 365 days but the CANCEL can't go over effectiveenddatetime-effectivestartdatetime so.....

For CANCEL if picturedate is earlier than effectiveenddatetime I need to do picturedatetime-effectivestartdatetime, if picturedatetime is later than effectiveenddatetime I need to do effectiveenddatetime-effectivestartdatetime.!!

Don't know if this makes sense or not?