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,
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.
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?