Historical and future values with restriction of filter

[florian_bertsch]

Hi Qlik community,

I aim at the following:

I load data for some calendar weeks in one fact table.

Some data are historical data and some date are future data.

Measure 1 --> available for

2018-32 --> past

2018-33 --> current

Measure 2 --> available for

2018-32 --> past

2018-33 --> current

2018-34 --> future

2018-35 --> future

When I load everything in one fact table, the filter shows every calendar week:

2018-32

2018-33

2018-34

2018-35

But the user should not be able to choose a calendar week in the future (only calendar weeks up to 2018-33). But the future values should be visible in some charts.

I think the only solution is to split Measure 2 in 3 measures?

Calendar Week | Value | Value Week+1 | Value Week+2

2018-32

2018-33

Best regards,

Florian

[marcus_sommer]

I think you could just use for your users a second week-field which is restricted to the past/current data - maybe with something like this: if(YourDateField > today(), null(), week(YourDateField).

- Marcus

[mjamesking]

Hi,

Include a field alongside your week dates that says whether it is 'Historic', Current' or 'Future'. Put an expression in your filter pane along the lines of

=If(Week_Type = 'Historic' OR Week_Type = 'Current', Week)

This will only include the Historic and Current dates in the pane

Then in your charts just use the week dimension and all weeks should get included

You could add a colour expression to your chart to colour the line (or bars) a different colour for the Future weeks

e.g.

= If(Week_Type = 'Future', RGB(255, 0, 0))

[florian_bertsch]

Hi Marcus,

I can't restrict my filter with your calculated dimension, because then getFieldSelections(filter) is not working. In the filter pane, I need the original week dimension.

Best regards,

Florian

[florian_bertsch]

Hi Matt,

I can't restrict my filter with your calculated dimension, because then getFieldSelections(filter) is not working. In the filter pane, I need the original week dimension.

Best regards,

Florian

[mjamesking]

Yeah, that won't work. Why are you retrieving the selections for the week dimension (out of curiosity)?

[mjamesking]

How about using

=Concat(Week, ',')

To retrieve the possible selections for Week, this will work with the calculated dimension filter

Matt

[marcus_sommer]

If you show the future weeks within the filter pane the user could (try) to select them - even if you restored the previous selection-state (in View you could use actions for it and I assume that there are some extensions in Sense which provide similar features) or if you exclude these values within your expressions (maybe with any set analysis) - it would not very user-friendly.

Therefore my suggestion about using a second specialized dimension - and picking the selected or possible dimension-values could be done with concat() or p().

- Marcus