Date Aggregation for Year N-1 in Qlik Application

mehdi_asma — Mon, 15 Jul 2024 09:53:11 GMT

Hello Qlik Community,

I have a Qlik application with multiple filters (Year, Month, Date, InternalRef) and two dropdowns that update my variables v_heureDebut & v_heureFin. My dataset contains the number of clients at half-hour intervals, and I need to calculate the average per date. I created the following measure:

AVG(
Aggr(
sum({<heureDebut = {"=heureDebut >= '$(v_heureDebut)'"}, heureFin = {"=heureFin <= '$(v_heureFin)'"}>} clients),
date, Boulangerie
)
)

This works perfectly for the current year (Year N). However, I encounter aggregation issues when I try to perform a similar calculation for the previous year (Year N-1). I have two scenarios to handle:

Only one date is selected.
Multiple dates are selected.
Here's the expression I am using:

Only one date is selected.
Multiple dates are selected.
Here's the expression I am using:

If(GetSelectedCount(date) = 1,
AVG(
Aggr(
sum(
{<
heureDebut = {"=heureDebut >= '$(v_heureDebut)'"},
heureFin = {"=heureFin <= '$(v_heureFin)'"},
typeTicket = {0, 200, 201},
annee=,
mois=,
jour=,
date = {"$(=Date(min(date) - 364, 'DD/MM/YYYY'))"}
>}
clients
),
refInterne
)
),
AVG(
Aggr(
sum(
{<
heureDebut = {"=heureDebut >= '$(v_heureDebut)'"},
heureFin = {"=heureFin <= '$(v_heureFin)'"},
typeTicket = {0, 200, 201},
annee=,
mois=,
jour=,
date = {">=$(=Date(AddYears(min(date), -1), 'DD/MM/YYYY')) <=$(=Date(AddYears(max(date), -1), 'DD/MM/YYYY'))"}
>}
clients
),
refInterne, date
)
)
)

The first scenario works well since there is no need for date aggregation with a single selected date. However, in the second scenario, when aggregating by date and refInterne, it fails.

I am not sure why this happens and would appreciate any insights or solutions you might have.

Thank you in advance for your help!

topic Date Aggregation for Year N-1 in Qlik Application in App Development

Date Aggregation for Year N-1 in Qlik Application