Calculating a Year to Date figure

[#1]

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

[swuehl · Accepted solution]

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),

[thomas_skariah]

Hi Simon,

You can get an idea from the attached application.

Regards,

Tom