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