Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

Issue with set analysis calculation of dates period set

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

Peony_0-1733245163472.png

 

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

Peony_1-1733245769298.png

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

Labels (2)
1 Solution

Accepted Solutions
Clement15
Partner - Creator III
Partner - Creator III

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?

View solution in original post

2 Replies
Clement15
Partner - Creator III
Partner - Creator III

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?

Peony
Creator III
Creator III
Author

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