Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey there!
I'm using a pivot table, and I need to change a dimensions background color depending on the type of the event happening that day. Below is how the table should look according to what I mention.
Now, here is the data I'm using (its just a sample 😞
Fact | ||||
Region | Shipping Date | Delivery Date | #Units | %IdEvent |
NORTH | 13/06/2020 | 14/06/2020 | 2 | 1 |
NORTH | 14/06/2020 | 14/06/2020 | 1 | 1 |
SOUTH | 15/06/2020 | 17/06/2020 | 3 | 1 |
EAST | 16/06/2020 | 18/06/2020 | 1 | 2 |
WEST | 13/06/2020 | 14/06/2020 | 8 | 1 |
WEST | 16/06/2020 | 16/06/2020 | 4 | - |
Events | ||
%IdEvent | Event | Color |
1 | A | RED() |
2 | B | BLUE() |
So, I cant come with the right set analysis to get the background color to work as expected. Can anyone maybe point me to the right direction ?
Thanks!
Hey @Taoufiq_Zarra - thanks for taking your time to check this out. I tried what you mention and it didnt work according to my data. I mentioned the data posted was only a sample, and probably should have provided more info about the real one. Here are more details about it:
* There are more than 50 events during the year, and there could be a few different depending on the year. Thus, using that many nested IF's would be crazy.
* The background color depends on the type of event. There are 3.
* The table should only show 30 days (Delivery Date) to the past according to a user selection, and the month (shipping date) also decided by the user.
Taking the above into account, what I have now is an event table as follow (it is again a sample):
%IdEvent | Event Name | _EventType |
1 | A | 0 |
2 | B | 1 |
3 | C | 2 |
4 | D | 1 |
5 | E | 0 |
This is basically my set analysis:
SUM( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} #Units )
As per the BG color expression. This is the one I have:
IF( DIMENSIONALITY() <> 0,
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 0, green(),
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 1, yellow(),
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 2, blue()
)))
)
The BG color would work if the first Shipping date holds a value, if it doesnt, then it wouldnt as I'm showing below.
What I noticed is that adding the dimension _EventType next to the [Event Name], would force it to set the BG color.
Thus, for now what I'm doing is to set the _EventType font color to white and reduce the column width to the minimum so the user wouldnt notice it. I know this isnt the best solution, but it is working for me now. Nevertheless, I would love to know which should be the right way.
Regards,
Diego
unchecking 'Suppress Zero-Values' on the presentation tab
I tested for example Event->background :
=if(Event='A',red(),if(Event='B',Blue()))
if check :
unchecking
Hey @Taoufiq_Zarra - thanks for taking your time to check this out. I tried what you mention and it didnt work according to my data. I mentioned the data posted was only a sample, and probably should have provided more info about the real one. Here are more details about it:
* There are more than 50 events during the year, and there could be a few different depending on the year. Thus, using that many nested IF's would be crazy.
* The background color depends on the type of event. There are 3.
* The table should only show 30 days (Delivery Date) to the past according to a user selection, and the month (shipping date) also decided by the user.
Taking the above into account, what I have now is an event table as follow (it is again a sample):
%IdEvent | Event Name | _EventType |
1 | A | 0 |
2 | B | 1 |
3 | C | 2 |
4 | D | 1 |
5 | E | 0 |
This is basically my set analysis:
SUM( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} #Units )
As per the BG color expression. This is the one I have:
IF( DIMENSIONALITY() <> 0,
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 0, green(),
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 1, yellow(),
IF( ONLY( { <_DataSet = {$(fHistoricalData),$(fFutureData)}, [Delivery Date] = {">=$(ui30DaysBefore) <=$(uiMaxDate)"}, [Shipping Year] = UserDate::[Stay Year], [Shipping Month] = UserDate::[Stay Month]>} _EventType ) = 2, blue()
)))
)
The BG color would work if the first Shipping date holds a value, if it doesnt, then it wouldnt as I'm showing below.
What I noticed is that adding the dimension _EventType next to the [Event Name], would force it to set the BG color.
Thus, for now what I'm doing is to set the _EventType font color to white and reduce the column width to the minimum so the user wouldnt notice it. I know this isnt the best solution, but it is working for me now. Nevertheless, I would love to know which should be the right way.
Regards,
Diego
fyes this trick will work well, what is important is to avoid null values after this Event Name Field
Just one additional comment, maybe in this case replacing the nulls with value of 0 might be the better option on this application, just wanted to toss that out as an idea too. I am going to mark your post with what you did as the solution, thanks to Taoufiq for the suggestions and confirmation on things too.
Maybe this help link will better explain:
Cheers,
Brett