Issue with set analysis calculation of dates period set

Peony — Tue, 03 Dec 2024 17:19:40 GMT

Hi all. Could someone please help me to clarify why my calculation doesn't work for all cases I tested.
What I need to do:
1. I need to calculate the list of dates that are included in the period selected by the user. The user can select in the fields Year, Quarter, Month, Week, Day. Like period from 25/10/2024 to 22/10/2024.

2. Than I need to calculate the number of days that are included in the selected period. For period from 25/10/2024 to 22/10/2024 it is gonna be 4 days.

3. I need to count previous day before earliest date in the period. It gonna be 21/10/2024 - max date of the new calculated period

4. Then I subtract 4 days from the 21/10/2024 and calculate the last date of the new period. It will be 18/10/2024.

Here how result looks like

All issues starts when I use selection only for several filters. Lets say I I select Year = 2024, Quarter = Q4, Month = October. In this case list of dates from selected period will be from 31/10/2024 to 01/10/2024. Amount of days for this period is 31. So I expected to get results for calculated period 30/09/2024 to 31/08/2024. But in fact I received nothing

As far as I see, it happens because 31/08/2024 is relate to Q3. And till I have Q4 selected I will receive empty results. But I excluded possible field selections in set analysis for calculated period and I don't understand why it does not work.
My formula is
=Concat({<Day=, Week= ,Month =, Quater =, Year =, OrderDateKey={">$(=$(vComparedDateMin))<=$(=$(vComparedDateMax))"}>}OrderDate, ' ')

OrderDateKey - is field that contained numeric value of date
vComparedDateMin - earliest date in a new periods set
vComparedDateMax - latest date in a new periods set
OrderDate - formated date values

I also add .qvf file (created in QlikView so I'm not sure if Qlik Sense will work correctly with it)
I'll be very appreciate for any help or hint

Re: Issue with set analysis calculation of dates period set

Clement15 — Wed, 04 Dec 2024 14:55:39 GMT

Hello,

There is a mistake in your formula, you forgot an r in quarter.

You can use this formula:

=Concat({<Day=, Week= ,Month=, Quarter=, Year=, OrderDateKey={">$(=$(vComparedDateMin))<=$(=$(vComparedDateMax))"}>}OrderDate, ' ')

Is this the result you want?

Re: Issue with set analysis calculation of dates period set

Peony — Wed, 04 Dec 2024 18:24:14 GMT

@Clement15 You are totally right! A typo issue that costs me a lot of nerves. Thank you for brining light into this typo darkness.

topic Issue with set analysis calculation of dates period set in App Development

Issue with set analysis calculation of dates period set

Re: Issue with set analysis calculation of dates period set

Re: Issue with set analysis calculation of dates period set