Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

Issue with set analysis calculation of dates period set (filters affect results)

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 .qvw file 
I'll be very appreciate for any help or hint

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@Peony  below should work

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

View solution in original post

4 Replies
marcus_sommer

Normally it should be working - maybe your issue is just a typo because Quater <> Quarter. If this isn't the cause then move the expression from the text-box to a table-chart and leave the expression-label blank. Then hover on this label to see how the used variables are resolved - there may any mistakes.

Kushal_Chawda

@Peony  below should work

=Concat({<Year=,Quarter=,Month=, OrderDateKey={">$(vComparedDateMin)<=$(vComparedDateMax)"}>}OrderDate, ' |')
Peony
Creator III
Creator III
Author

@Kushal_Chawda Thank you! 🤗It works as should now!

Peony
Creator III
Creator III
Author

@marcus_sommer  Yeap! It was a silly typo issue. Thank you for notice and for the additional option how to check issue.