Actual vs Budget Conditional Amount

[microwin88x]

Hello,

I have the following table:

(Note that the SOURCE could be ACTUAL or BUDGET)

YEAR	PERIOD	TYPE	SOURCE	AMOUNT
2013	201307	INCOME	ACTUAL	2.454
2013	201308	INCOME	ACTUAL	2.063
2013	201309	INCOME	ACTUAL	2.072
2013	201310	INCOME	ACTUAL	2.049
2013	201311	INCOME	ACTUAL	2.092
2013	201312	INCOME	ACTUAL	2.685
2014	201401	INCOME	ACTUAL	2.060
2014	201402	INCOME	ACTUAL	2.095
2014	201403	INCOME	ACTUAL	2.342
2014	201404	INCOME	ACTUAL	2.452
2014	201405	INCOME	ACTUAL	2.509
2014	201406	INCOME	ACTUAL	2.380
2014	201407	INCOME	ACTUAL	2.933
2014	201408	INCOME	ACTUAL	2.670
2014	201409	INCOME	ACTUAL	2.710
2014	201410	INCOME	ACTUAL	2.849
2014	201411	INCOME	ACTUAL	2.645
2014	201412	INCOME	ACTUAL	4.148
2015	201501	INCOME	ACTUAL	3.361
2015	201502	INCOME	ACTUAL	2.719
2015	201503	INCOME	ACTUAL	3.856
2015	201504	INCOME	ACTUAL	3.233
2015	201505	INCOME	ACTUAL	3.955
2015	201506	INCOME	ACTUAL	4.042
2015	201507	INCOME	ACTUAL	4.697
2015	201508	INCOME	ACTUAL	4.268
2015	201509	INCOME	ACTUAL	3.448
2015	201510	INCOME	ACTUAL	3.826
2015	201511	INCOME	ACTUAL	3.486
2015	201512	INCOME	ACTUAL	6.313
2016	201601	INCOME	ACTUAL	5.046
2016	201602	INCOME	ACTUAL	4.051
2016	201603	INCOME	ACTUAL	4.288
2016	201604	INCOME	ACTUAL	4.554
2016	201605	INCOME	ACTUAL	5.163
2016	201606	INCOME	ACTUAL	5.550
2016	201607	INCOME	ACTUAL	6.475
2016	201608	INCOME	ACTUAL	5.174
2016	201609	INCOME	ACTUAL	4.732
2016	201610	INCOME	ACTUAL	4.946
2016	201611	INCOME	ACTUAL	5.017
2016	201612	INCOME	ACTUAL	6.879
2017	201701	INCOME	ACTUAL	4.957
2017	201702	INCOME	ACTUAL	5.306
2017	201703	INCOME	ACTUAL	5.715
2017	201704	INCOME	ACTUAL	6.656
2017	201705	INCOME	ACTUAL	6.366
2017	201706	INCOME	ACTUAL	6.432
2017	201707	INCOME	BUDGET	8.051
2017	201707	INCOME	ACTUAL	8.161
2017	201708	INCOME	BUDGET	6.244
2017	201708	INCOME	ACTUAL	6.595
2017	201709	INCOME	BUDGET	5.542
2017	201709	INCOME	ACTUAL	3.915
2017	201710	INCOME	BUDGET	6.087
2017	201711	INCOME	BUDGET	5.943
2017	201712	INCOME	BUDGET	7.890
2018	201801	INCOME	BUDGET	5.899
2018	201802	INCOME	BUDGET	6.056
2018	201803	INCOME	BUDGET	6.832
2018	201804	INCOME	BUDGET	6.608
2018	201805	INCOME	BUDGET	6.588
2018	201806	INCOME	BUDGET	6.512

What I need is to create a bar chat with a drill down dimension with YEAR and PERIOD like this:

I should consider, from 201708 (last month) and previous periods with AMOUNT for SOURCE = ACTUAL.

For example: 201307, 201308, 201309, ..., 201706, 201707, 201708 should use only the ACTUAL value.

From 201709 (current month in progress) and later months, I use AMOUNT for SOURCE = BUDGET.

So if I filter YEAR = 2017, then I would need something like this (blue is ACTUAL / grey is BUDGET😞

These are the values I need to use to build the chart for YEAR = 2017:

YEAR	PERIOD	TYPE	SOURCE	AMOUNT
2017	201701	INCOME	ACTUAL	4.957
2017	201702	INCOME	ACTUAL	5.306
2017	201703	INCOME	ACTUAL	5.715
2017	201704	INCOME	ACTUAL	6.656
2017	201705	INCOME	ACTUAL	6.366
2017	201706	INCOME	ACTUAL	6.432
2017	201707	INCOME	ACTUAL	8.161
2017	201708	INCOME	ACTUAL	6.595
2017	201709	INCOME	BUDGET	5.542
2017	201710	INCOME	BUDGET	6.087
2017	201711	INCOME	BUDGET	5.943
2017	201712	INCOME	BUDGET	7.890

And YEAR = 2018 would be all with SOURCE = BUDGET by now.

I attach the QVW. Do you know how could I do that?

Thank you!

[dinuwanbr]

Hi,

PFA and change the rgb value in background color in the expression to grey as u want (unfortunately I put violet)

Rgds,

Tharindu

[microwin88x]

Hello,

I'm afraid I don't get the desired output. Because if you check the table for YEAR = 2017:

YEAR	PERIOD	TYPE	SOURCE	AMOUNT
2017	201701	INCOME	ACTUAL	4.957
2017	201702	INCOME	ACTUAL	5.306
2017	201703	INCOME	ACTUAL	5.715
2017	201704	INCOME	ACTUAL	6.656
2017	201705	INCOME	ACTUAL	6.366
2017	201706	INCOME	ACTUAL	6.432
2017	201707	INCOME	ACTUAL	8.161
2017	201708	INCOME	ACTUAL	6.595
2017	201709	INCOME	BUDGET	5.542
2017	201710	INCOME	BUDGET	6.087
2017	201711	INCOME	BUDGET	5.943
2017	201712	INCOME	BUDGET	7.890

On the other side, 201709 is bringing ACTUAL value and not BUDGET:

[dinuwanbr]

Hi Macro,

Check this.

I think it's bit tricky dealing with both year and month in the same graph since we can't use aggr() for PERIOD in Year dimension. Still I'm working on that.

Rgds,

Tharindu

[antoniotiman]

Hi,

see Attachment.

Regards,

Antonio.