Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.