Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Pivot Dimension Background color workaround

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.

 

tables.png

Now, here is the data I'm using (its just a sample 😞

Fact
RegionShipping DateDelivery Date#Units%IdEvent
NORTH13/06/202014/06/202021
NORTH14/06/202014/06/202011
SOUTH15/06/202017/06/202031
EAST16/06/202018/06/202012
WEST13/06/202014/06/202081
WEST16/06/202016/06/20204-

 

Events
%IdEventEventColor
1ARED()
2BBLUE()

 

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!

 

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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

%IdEventEvent Name_EventType
1A0
2B1
3C2
4D1
5E0

 

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.

 

Captura.PNG

 

 What I noticed is that adding the dimension _EventType next to the [Event Name], would force it to set the BG color.

tables.png

 

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

 

View solution in original post

4 Replies
Highlighted
Master
Master

@digichap28 

unchecking 'Suppress Zero-Values' on the presentation tab

Capture.PNG

I tested for example Event->background :

=if(Event='A',red(),if(Event='B',Blue()))

 

if check :

Capture1.PNG

unchecking

Capture2.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor III
Contributor III

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

%IdEventEvent Name_EventType
1A0
2B1
3C2
4D1
5E0

 

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.

 

Captura.PNG

 

 What I noticed is that adding the dimension _EventType next to the [Event Name], would force it to set the BG color.

tables.png

 

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

 

View solution in original post

Highlighted
Master
Master

fyes this trick will work well, what is important is to avoid null values after this Event Name Field

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Digital Support
Digital Support

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:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.