Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculating a Year to Date figure

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

2 Replies
MVP
MVP

Re: Calculating a Year to Date figure

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
Contributor III

Re: Calculating a Year to Date figure

Hi Simon,

You can get an idea from the attached application.

Regards,

Tom

Community Browser