Selection of Month-End / Quarter-End / Year-End via a single selection

Vladimir_Ermakov — Fri, 17 Apr 2020 08:20:34 GMT

Dear colleagues, good morning. Please, advise on the following topic.

I have a table with a timeline. I need to provide the user with a single filter-pane that will allow him to keep in the final table

- all dates

- only month-end dates

- only quarter-end dates

- only year-end dates.

I've created the fields "daily", "month-end", "quarter-end" and "year-end" in the data load script, and they show flags 1 or 0. However, this approach results in 4 selections instead of in 1.

Date	Daily	Month-End	Quarter-End	Year-End	Data
31.12.2019	1	1	1	1	...
01.01.2020	1	0	0	0	...
31.01.2020	1	1	0	0	...
...	...	...	...	...	...
31.03.2020	1	1	1	0	...

Alternatively, I can create a qualifier field, that will be a sum of flags, so it will show 4 for year-ends, 3 for quarter-ends, 2 for month-ends and 1 for other days. But then I somehow need to include ">=" into the dimension, because otherwise when the user chooses "1" for daily reporting, it will exclude month-ends, quarter-ends, and year-ends; and I don't know how to do that.

In SQL I would have solved that by creating a mapping table and left-join'ing it to the base table with a ">=" in ON operator; that would create extra rows, but still do the trick. However, as far as I understand, QlikSense only allows to JOIN / KEEP on an explicit match.

Would be grateful for any suggestions. Thank you.

Re: Selection of Month-End / Quarter-End / Year-End via a single selection

Vladimir_Ermakov — Thu, 23 Apr 2020 18:33:31 GMT

OK, I've received no reply, so I've solved it myself.

To the experienced users, this must be a pretty obvious solution, but I'll just post it here for the future reference of poor souls like me.

In short, I've kept the flag structure in my Facts table just as reflected above. I've also created a composite key for Daily|MonthEnd|QuarterEnd|YearEnd

Date	DayFlag	MonthEndFlag	QuarterEndFlag	YearEndFlag	Data
31.12.2019	1	1	1	1	...
01.01.2020	1	0	0	0	...
31.01.2020	1	1	0	0	...
...	...	...	...	...	...
31.03.2020	1	1	1	0	...

Then I've created two more tables:

_ReportPeriod:

_Period	_PeriodID
Day	1
Month	2
Quarter	3
Year	4

and the bridge to keep them together, also with a composite key to link to the Fact table.

_PeriodId	DayFlag	MonthEndFlag	QuarterEndFlag	YearEndFlag
1	1	0	0	0
1	1	1	0	0
1	1	1	1	0
1	1	1	1	1
2	1	1	0	0
2	1	1	1	0
2	1	1	1	1
3	1	1	1	0
3	1	1	1	1
4	1	1	1	1

So, this works like a charm: the user has a filter pane for field _Period, and when he chooses a particular one, only the required Dates are retained in the visualisations and other filter panes.

topic Re: Selection of Month-End / Quarter-End / Year-End via a single selection in Visualization and Usability

Selection of Month-End / Quarter-End / Year-End via a single selection

Re: Selection of Month-End / Quarter-End / Year-End via a single selection