Set Analysis

[Stockmann]

Hi everyone,

I’m working on a Qlik Sense dashboard where users can compare sales or visitor counts between two custom date ranges using four variables:

vstart1, vend1 (for the first period)
vstart2, vend2 (for the second period)
My requirement is:

If either of the selected date ranges falls within the period from 01-July-2024 to 25-Oct-2024, I want to multiply the visitor count by 0.8 for that range.

I tried using an IF condition inside the set expression, but I know that’s not supported directly in set analysis. Here's what I attempted:

IF(
    (vstart1 >= '2024-07-01' AND vend1 <= '2024-10-25'),
    count({<Date={">=$(=vstart1)<=$(=vend1)"}>} v_id) * 0.8,
    count({<Date={">=$(=vstart1)<=$(=vend1)"}>} v_id)
)

[marcus_sommer · Accepted solution]

Your if-approach should in general be working - at least if the variables in all calls are properly interpreted which means in regard to the place in which they are called. Such stuff is quite error-prone and should be therefore avoided by using only pure numbers.

The if-loop might be shortened with an approach like:

count({<Date={">=$(=vstart1)<=$(=vend1)"}>} v_id) *
IF(vstart1 >= '2024-07-01' AND vend1 <= '2024-10-25', 0.8, 1)

Beside the above I suggest to consider to transfer the factor-stuff into the data-model by assigning the relevant factor to each date - within a separate dimension-table or maybe within the calendar - and then using something like:

count({<Date={">=$(=vstart1)<=$(=vend1)"}>} v_id * factor)

or

count({<Date={">=$(=vstart1)<=$(=vend1)"}>} v_id) *
avg({<Date={">=$(=vstart1)<=$(=vend1)"}>} factor)

and/or wrapping them into aggr() to consider a certain dimensionality.