Gradient fill stacked bar chart where Dimension is Day of month

simon_ball — Thu, 05 Jul 2018 08:16:00 GMT

Hello.

I have been asked to mimic and existing report for a new customer.

I'm documenting the solution I used here but i'm interested in Qlik experts critique and also enhancements.

I'm working from test data at the moment.

The issue is that they have a series of services (each bar) and they want to see how much each service is being used per day in a given month.

The bar chart is fairly simple, the dimensions are

A) the service (fleet 1..n)

B) the date field from the calendar

the expression (%JOIN_CALENDAR)

c) Count (Distinct workitems)

The issue is that the default colours loop after 15.

The clients corporate colours re white, blue and orange, so i used colour picker to get the # hex for their blue and orange and put it into a colour gradient generator to make a nice colour gradient with 31 steps.

I copied the data across into excel and used the formula =HEX2DEC(MID(C1,1,2)) to split it, and then used

="SET v_Colour_"&A1&" = 'RGB(" & E1& ", " & F1&", "&G1&")';" to automate the variables.

e.g.

SET v_Colour_1 = 'RGB(50, 76, 124)';

I placed the variables in the load script, looking at it now i should have been let not set?

in the expression i set the background colour as follows,

=if(Day(%JOIN_CALENDAR)='1',$(v_Colour_1), if(Day(%JOIN_CALENDAR)='2',$(v_Colour_2),

if(Day(%JOIN_CALENDAR)='3',$(v_Colour_3), if(Day(%JOIN_CALENDAR)='4',$(v_Colour_4),

if(Day(%JOIN_CALENDAR)='5',$(v_Colour_5), if(Day(%JOIN_CALENDAR)='6',$(v_Colour_6),

if(Day(%JOIN_CALENDAR)='7',$(v_Colour_7), if(Day(%JOIN_CALENDAR)='8',$(v_Colour_8),

if(Day(%JOIN_CALENDAR)='9',$(v_Colour_9), if(Day(%JOIN_CALENDAR)='10',$(v_Colour_10),

if(Day(%JOIN_CALENDAR)='11',$(v_Colour_11), if(Day(%JOIN_CALENDAR)='12',$(v_Colour_12),

if(Day(%JOIN_CALENDAR)='13',$(v_Colour_13), if(Day(%JOIN_CALENDAR)='14',$(v_Colour_14),

if(Day(%JOIN_CALENDAR)='15',$(v_Colour_15), if(Day(%JOIN_CALENDAR)='16',$(v_Colour_16),

if(Day(%JOIN_CALENDAR)='17',$(v_Colour_17), if(Day(%JOIN_CALENDAR)='18',$(v_Colour_18),

if(Day(%JOIN_CALENDAR)='19',$(v_Colour_19), if(Day(%JOIN_CALENDAR)='20',$(v_Colour_20),

if(Day(%JOIN_CALENDAR)='21',$(v_Colour_21), if(Day(%JOIN_CALENDAR)='22',$(v_Colour_22),

if(Day(%JOIN_CALENDAR)='23',$(v_Colour_23), if(Day(%JOIN_CALENDAR)='24',$(v_Colour_24),

if(Day(%JOIN_CALENDAR)='25',$(v_Colour_25), if(Day(%JOIN_CALENDAR)='26',$(v_Colour_26),

if(Day(%JOIN_CALENDAR)='27',$(v_Colour_27), if(Day(%JOIN_CALENDAR)='28',$(v_Colour_28),

if(Day(%JOIN_CALENDAR)='29',$(v_Colour_29), if(Day(%JOIN_CALENDAR)='30',$(v_Colour_30),

if(Day(%JOIN_CALENDAR)='31',$(v_Colour_31), RGB(0,0,0)) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ))

This is the chart:

So, what can I do to optimise the 31 stage Nested If in the expression?

Can I use a function to accept a day number and return a variable name or RGB() string?

If so, How?

Re: Gradient fill stacked bar chart where Dimension is Day of month

ogautier62 — Thu, 05 Jul 2018 08:28:02 GMT

Hi,

you could put it in your master calendar during the load ?

regards

Re: Gradient fill stacked bar chart where Dimension is Day of month

simon_ball — Thu, 05 Jul 2018 11:15:32 GMT

how so?

set up a day number inline table mapping load and use an applymap to link month day to the RGB code as DayColour... then just use the DayColour field direct in the backgroundcolour?

Re: Gradient fill stacked bar chart where Dimension is Day of month

ogautier62 — Thu, 05 Jul 2018 11:19:11 GMT

yes I think so

or just a new 31 rows table with daynumber, rgb color

add daynumber in calendar if not present

topic Re: Gradient fill stacked bar chart where Dimension is Day of month in QlikView

Gradient fill stacked bar chart where Dimension is Day of month

Re: Gradient fill stacked bar chart where Dimension is Day of month

Re: Gradient fill stacked bar chart where Dimension is Day of month

Re: Gradient fill stacked bar chart where Dimension is Day of month