Tables relationship / calendar

virilo_tejedor — Sat, 16 Nov 2024 20:32:58 GMT

I'd like to show calendar info as background in a line chart.

I've found this feature is not implemented in QlikSense.

So, I'm using a combo chart

- foreground line in blue is the aggregated data - let's say count(request_id)

- background red bar is the calendar info

It is working as the picture shows.

But I'm not very sure if I'm joining this info in the right way

TABLE_1 WEB_REQUESTS

   - request_id
   - request_date
   - request_ ...

TABLE_2 CALENDAR

- request_date
- calendar_type_of_day = {'WEEKEND', 'PUBLIC_HOLIDAY', 'REDUCED_WORKING_DAY'}

[autoCalendar]

...

DERIVE FIELDS FROM FIELDS request_date, ...

I used request_date as date for CALENDAR table, in order QlikSense engine to link both informations.

But there are side effects, like

- If a show a table with the calendar info, it's plenty of request_dates having calendar_type_of_day=Null.
I've to solve it, excluding these null values.

- And if I select 'WEEKEND' in a selector, the blue line, count(request_id) it stops working like in the next picture

So I'm not very sure if I'm doing the relationship in the correct way.

I've thought to use ApplyMap to add an extra column to TABLE_1 calendar_type_of_day.

What's the correct way to perform this relationship?
Is there a better way to use a background color for holidays, weekends, etc?

Thanks in advance

topic Tables relationship / calendar in App Development

Tables relationship / calendar