topic Variable YTD Budget in QlikView

Variable YTD Budget

Thu, 18 Sep 2014 02:06:41 GMT

I am trying to calculate the YTD budget based on the selection of the user.

I have a selection for the months (Jan - Dec).

If no month is selected, the YTD budget should only show up to a given month (set in a variable), which is the current actual. If one or more months are selected, than the sum of the selected should be shown.

I tried the following expression:

=Sum({$<SourceType={'Budget'}, Month={'=If(GetSelectedCount(Month)=0,4,GetSelectedCount(Month))'}>} If(AC3='510', BalanceLC))

4 in this example is the current actual month. So if no month is selected, I would like to show the sum of the budget values that are <= to 4 (i.e. 1 - 4). If I select a month, I want to show the sum of the selection. The above selection works fine, except that when I don't select a month, it shows me the full year budget instead of the 4 months only.

What is wrong in my expression?

Re: Variable YTD Budget

jagan — Thu, 18 Sep 2014 02:13:42 GMT

Hi,

Try like this

create a variable

vCurrentMonth = 4

If(GetSelectedCount(Month)=0,

Sum({$<SourceType={'Budget'}, Month={'<=$(=vCurrentMonth)'}, AC3={'510'}>} BalanceLC),

Sum({$<SourceType={'Budget'}, AC3={'510'}>} BalanceLC))

Hope this helps you.

Regards,

Jagan.

Re: Variable YTD Budget

Thu, 18 Sep 2014 02:21:07 GMT

Dear Jagan

Thanks, I tried that, yet the result is the same...

Re: Variable YTD Budget

jagan — Thu, 18 Sep 2014 02:23:18 GMT

Hi,

This should work, if not working attach some sample file and expected output.

If(GetSelectedCount(Month)=0,

Sum({$<SourceType={'Budget'}, Month={4}, AC3={'510'}>} BalanceLC),

Sum({$<SourceType={'Budget'}, AC3={'510'}>} BalanceLC))

Regards,

Jagan.

Re: Variable YTD Budget

Thu, 18 Sep 2014 03:50:37 GMT

Thanks, that does the trick, although it is more complicated that I had hoped for.