Skip to main content
Topics#Reports & DashboardsLiezele Dizon-Tosco's Question
Liezele Dizon-Tosco (Rieze) asked in #Reports & Dashboards
Aug 8, 2018, 11:56 AM

Growth Rate Year After Year

I'd appreciate any help. I'm trying to get a total of our membership year after using this formula: RowCount + PREVGROUPVAL(RowCount, CREATED_DATE). It worked for CY 2014 (723+1466 = 2189), but how do I create another formula to capture that for the following years. 

 

For example CY2015 should be 723+1466+908=3097, CY2016 should be 723+1466+908 +1404=4501, etc.

 

Thanks!Growth Rate Year After Year
2 answers
  1. Sunil Sarilla (M&G Investments)
    Aug 8, 2018, 12:44 PM
    Hi Liezele,

     

    do you need to display that on the report, are you ok to have this info in the form of a chart so that you can use a cumulative chart.

     

    If you want it in the form of a report, then you will need to increment paramter (be defautl the increment back is 1)

     

    looking at the screenshot if you want the cumulative membership for 2018, you will need to go back 5 increments

     

    please try the below formula in the custom summary formula

    RowCount +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,1),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,2),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,3),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,4),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,5),0)

    you will have to change the formula for next year as you will hve entries for 2019

     

    PS: This formula will not work as soon as you have one more entry under the Chapter Lookup, say you have one more entry for Dallas, TX under Chapter Lookup grouping, then the formula will be

    RowCount +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,1),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,2),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,3),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,4),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,5),0) +
    BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,6),0) + BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,7),0) + BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,8),0) + BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,9),0) + BLANKVALUE(PREVGROUPVAL(RowCount, CREATED_DATE,10),0)
Write an answer...
0/9000