Dynamic Year‑to‑Date and Month Range Set Analysis in

[LoKi_asterix]

Hi everyone,

I’m working in QlikView and need help building a set analysis measure that adjusts its date range dynamically based on Year and Month selections.

Here’s the behavior I’m aiming for:

No Year and no Month selected → Show current year‑to‑date (from 1 Jan of the current year up to today).

Year selected, no Month → Show year‑to‑date for that year (from 1 Jan up to the latest available date in that year).

Year and Month(s) selected → Show from the first day of the earliest selected month through the last day of the latest selected month.

Example: Selecting Year = 2025 with Oct, Nov, and Dec should return 01/10/2025 to 31/12/2025.

[Daniel_Castella]

Hi @LoKi_asterix 

I think you need to try something like this:

Sum({1<[Year_Field]={"$(=Year(Max({1<[Year_Field]=$::[Year_Field]>} [Year_Field])))"}, [Month_Field]={"<=$(=(Max({1< [Month_Field]=$::[Month_Field]>}[Month_Field]))) >=$(=(Min({1< [Month_Field]=$::[Month_Field]>}[Month_Field])))"}>} Data_Field)

What the formula does it is always selecting the max Year in Year_Field.

• If no year is selected, it will filter by the current year (unless you have future dates in you data, then other adjustment will be needed).
• If a year is selected, it automatically will become the max year in Year_field. Hence, the set analysis will filter for that year.

For month it is similar:

• If no month is selected, it will filter between the min month and the max month, that will be from 1 Jan up to the latest available date in that year.
• If a month or some months are selected, the min and max will become that selected months and it will filter from the first day of the earliest selected month through the last day of the latest selected month

Also, both Year and Month calculations ignore all the selections except the ones made in Year_Field and Month_Field.

Let me know if this formula works for you.

Kind Regards

Daniel