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

    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.





        • Capping a value
          Jason Michaelides

          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,



            • 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
                  Jason Michaelides

                  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?