topic Re: Pivot Dimension Background color workaround in QlikView

Pivot Dimension Background color workaround

digichap28 — Fri, 19 Jun 2020 20:06:14 GMT

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!

Re: Pivot Dimension Background color workaround

Taoufiq_Zarra — Sun, 21 Jun 2020 21:51:21 GMT

@digichap28

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

Re: Pivot Dimension Background color workaround

digichap28 — Tue, 23 Jun 2020 15:36:45 GMT

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

Re: Pivot Dimension Background color workaround

Taoufiq_Zarra — Tue, 23 Jun 2020 15:50:22 GMT

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

Re: Pivot Dimension Background color workaround

Brett_Bleess — Wed, 08 Jul 2020 17:53:41 GMT

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/ScriptRegularStatements/NullAsValue.htm

Cheers,
Brett