6 Replies Latest reply: Aug 11, 2016 6:51 AM by Sunny Talwar RSS

    Year On Year growth %

    Govind Ramchetty

      Hi

      I have 2 values Year and Total.

       

      I have to display in my chart report like Year on Year growth %.How can i achieve this.

       

      Thanks

      Govind R

        • Re: Year On Year growth %
          Gysbert Wassenaar

          Generally it's calculated using something like this: ValueOfYear2 / ValueOfYear1 - 1

          • Re: Year On Year growth %
            Gowtham Kesavan

            (Current Year - Previous Year)/Previous Year

            for Growth %

            • Re: Year On Year growth %
              Sunny Talwar

              Are you doing this with Year as your dimension? If you are, then you either need to use Above()/Below() functions, or you can use The As-Of Table to do it more efficiently.

                • Re: Year On Year growth %
                  Rohit Kumar

                  Hi Sunny, I have a sample script below and  using this expression: sum(Sales)-   above(sum(Sales))

                   

                  Can you please justify why there are few blank values and could you please provide me an example how to use above and below to calculate YOY%

                   

                  load

                  date(date#(Date_Feild,'DD-MM-YYYY'),'DD-MM-YYYY') as  Date_Feild,

                  Sales

                  INLINE [

                  Date_Feild, Sales

                  01-01-2015 ,10

                  01-02-2015 ,20

                  01-03-2015 ,10

                  01-04-2015 ,50

                  02-04-2015 ,100

                  03-04-2015 ,20

                  04-04-2015 ,30

                  01-05-2015 ,30

                  01-06-2015 ,30

                  01-07-2015 ,30

                  01-08-2015 ,30

                  09-08-2015 ,10

                  10-08-2015 ,10

                  01-01-2016 ,10

                  01-02-2016 ,20

                  01-03-2016 ,10

                  01-04-2016 ,30

                  02-04-2016 ,50

                  03-04-2016 ,10

                  04-04-2016 ,10

                  01-05-2016 ,30

                  01-06-2016 ,30

                  01-07-2016 ,30

                  01-08-2016 ,30

                  09-08-2016 ,10

                  10-08-2016 ,10

                  ];

                    • Re: Year On Year growth %
                      Sunny Talwar

                      Since we are looking at dates, the above()/below() may not be perfect and with missing entries, it can further complicate. Use As-Of Table is a better approach in this case.

                       

                      Script:

                      Table:

                      LOAD Date(Date#(Date_Field,'DD-MM-YYYY'),'DD-MM-YYYY') as  Date_Field,

                        Sales

                      INLINE [

                      Date_Field, Sales

                      01-01-2015 ,10

                      01-02-2015 ,20

                      01-03-2015 ,10

                      01-04-2015 ,50

                      02-04-2015 ,100

                      03-04-2015 ,20

                      04-04-2015 ,30

                      01-05-2015 ,30

                      01-06-2015 ,30

                      01-07-2015 ,30

                      01-08-2015 ,30

                      09-08-2015 ,10

                      10-08-2015 ,10

                      01-01-2016 ,10

                      01-02-2016 ,20

                      01-03-2016 ,10

                      01-04-2016 ,30

                      02-04-2016 ,50

                      03-04-2016 ,10

                      04-04-2016 ,10

                      01-05-2016 ,30

                      01-06-2016 ,30

                      01-07-2016 ,30

                      01-08-2016 ,30

                      09-08-2016 ,10

                      10-08-2016 ,10

                      ];

                       

                      LinkTable:

                      LOAD Date_Field as Report_Date_Field,

                        Date_Field,

                        'CY' as Flag

                      Resident Table;

                       

                      Concatenate(LinkTable)

                      LOAD Date_Field as Report_Date_Field,

                        Date(AddYears(Date_Field, -1), 'DD-MM-YYYY') as Date_Field,

                        'PY' as Flag

                      Resident Table;

                       

                      Capture.PNG