Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
PFA and change the rgb value in background color in the expression to grey as u want (unfortunately I put violet)
Rgds,
Tharindu
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:
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
Hi,
see Attachment.
Regards,
Antonio.