0 Replies Latest reply: May 28, 2015 4:34 PM by Paul Van Siclen RSS

    Compound Annual Growth Rate (CAGR)

    Paul Van Siclen

      GOAL: to allow user to identify the rate of growth across a portfolio based on any time selection and any combination of businesses

       

      Using the =RATE() function, I built a dynamic CAGR Analysis of a NYSE bank's loan portfolio.  The formula is a complex combination of the RATE() function and set analysis in order to make the user experience extremely simple.  Also note that once I verified the formula was working correctly, I put it in the Qlik Sense library so I could re-use.

       

      num(Rate(((max(quarterend([Date])) - min(quarterend([Date])))/365),0 ,
      (-1*num(avg({<[Date]={">=$(=min(quarterstart([Date])))<=$(=min(quarterend([Date])))"}>}[Amount]),'0.00')),
      num(avg({<[Date]={">=$(=max(quarterstart([Date])))<=$(=max(quarterend([Date])))"}>}[Amount]),'0.00')),

      '#,##0.00%')

       

      ***Note that this is the 3rd time I have re-cycled this formula for a similar purpose across different solutions

       

      Before (34 businesses across two divisions and no CAGR or exploration possible):

       

      bradesco3.png

       

      After (full exploration across any combination of business and/or time period):

       

      bradesco2.png

       

       

      There are two things to do if you plan on using this in Qlik Sense Desktop:

       

      1.  download the qvf to this directory:  qlik/sense/apps/

      2.  save the image files in this directory: qlik/sense/content/default/deck

       

       

       

       

      When you complete step 2, the Story Telling built into this app will work properly with all of the slides I have created to support the app.

       

      Enjoy.