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!
