2 Replies Latest reply: Dec 27, 2011 9:12 AM by Daniel Svendsen RSS

    Get last month revenue

      Hi All

       

      I´m having a issue in calculating a key figure that lookes like this "some amount / (this month revenue + last month revenue / 60)". The key figure is to be used in a line chart where month of year is the dimension.

       

      It is no problem getting data for "Some amount" and "This month revenue", however I can´t figure out how I get the revenue from the last of the current month you are currently calculating.

       

      My data is pre-aggregated to one record pr. month, so "Last month revenue" is always just one row. Don´t think that makes any difference, but now you know ;o)

       

      Hope one of you can help me

       

      And happy hollidays by the way ;o)

       

      /Daniel

        • Get last month revenue
          Stefan Wühl

          Daniel,

           

          one possible solution might be using chart inter record functions, like above().

           

          If you have a chart with dimension month of year and you get the revenue for a month with an expression like

          =sum( MonthRevenueField )

           

          then your expression could look like

          = sum( SomeAmountField) / ( sum( MonthRevenueField ) + above(sum( MonthRevenueField))  / 60 )

           

          (Not sure about the correct use of brackets around the division here, I tried to copy from your above post).

          Above function will evaluate the expression inside in the context of the dimension one row above (in a line chart with dimension month of year, this should be the previous month).

           

          For the very first month of year, you won't get a value for the previous month this way, so this might be a problem.

           

          Regards,

          Stefan