topic Re: Issue with set analysis calculation of dates period set (filters affect results) in QlikView

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

Peony — Tue, 03 Dec 2024 22:29:29 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 .qvw file
I'll be very appreciate for any help or hint

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

marcus_sommer — Wed, 04 Dec 2024 11:58:37 GMT

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.

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

Kushal_Chawda — Wed, 04 Dec 2024 16:51:15 GMT

@Peony below should work

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

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

Peony — Wed, 04 Dec 2024 18:18:15 GMT

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

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

Peony — Wed, 04 Dec 2024 18:21:12 GMT

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