2 Replies Latest reply: Oct 30, 2012 7:14 AM by Thomas Skariah RSS

    Calculating a  Year to Date figure

      Hi,

       

      I've written an expression to calculate a Year to date figure, i was just wondering if there was an easier way to do this?

       

      I currently do this:

       

       

      =

      IF(Month='Apr',Sum( {$<Month={Apr},Type={MonthTarget} >} Data),
      IF(Month='May',Sum( {$<Month={Apr,May},Type={MonthTarget} >} Data),
      IF(Month='Jun',Sum( {$<Month={Apr,May,Jun},Type={MonthTarget} >} Data),
      IF(Month='Jul',Sum( {$<Month={Apr,May,Jun,Jul},Type={MonthTarget} >} Data),
      IF(Month='Aug',Sum( {$<Month={Apr,May,Jun,Jul,Aug},Type={MonthTarget} >} Data),
      IF(Month='Sep',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep},Type={MonthTarget} >} Data),
      IF(Month='Oct',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct},Type={MonthTarget} >} Data),
      IF(Month='Nov',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct,Nov},Type={MonthTarget} >} Data),
      IF(Month='Dec',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec},Type={MonthTarget} >} Data),
      IF(Month='Jan',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan},Type={MonthTarget} >} Data),
      IF(Month='Feb',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb},Type={MonthTarget} >} Data),
      IF(Month='Mar',Sum( {$<Month={Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar},Type={MonthTarget} >} Data

      ),'Err'))))))))))))

       

      The reason I'm looking for another option is that I want to check another filed in the data which tells me whether its a '%' or number, then based on that value I need to change the data type of the result and the only way I know how to do this is to repeat the expression several times for each data format I want to check.

       

      Any ideas would be great

       

      Cheers

        • Re: Calculating a  Year to Date figure
          Stefan Wühl

          In which context are you using this expression? In a chart? With a Month dimension?

           

          You can look into pick() and match() function, using these you can get rid of the multiple if()-statements (though you would still have multiple expressions).

           

          If you can introduce a field in your data model that indicates the month number in your fiscal year (the year you defined starting from Apr, ending Mar), you can potentially use one expression (if you don't use the expression in a chart with dimension Month):

           

          Sum( {$<MonthNum = {"<=$(=max(MonthNum))"}, Month=,Type={MonthTarget} >} Data),

          • Re: Calculating a  Year to Date figure
            Thomas Skariah

            Hi Simon,

             

            You can get an idea from the attached application.

             

            Regards,

            Tom