Set analysis: Making a week measure responsive to a date filter.

[jyanik]

Hello! 

I am trying to create an expression that calculates the weekly range of surgeries taking place in a hospital, expressed as the minimum amount of surgeries in a day of that week, a '-' and the maximum amount of surgeries in a day of that week. 

The request is to put that expression on a KPI that shows the range for the max week by default, or the range of the corresponding week when a Selection has been made on the field 'Fecha' A date field on the format 'DD/MM/YYYY'

I wrote an expression that gives me the desired output when no selections have been made, but when I make a selection of the field Fecha (Date), it gives me the amount of surgeries on that day, twice, instead of the min and max surgeries of that week. 

Does anybody know how to make it so that the selection of the field Fecha brings me the output for its corresponding week, instead of that day?

Adding another field for filter is not an option as the request was that the Date field was the only filter on the App

My code so far is:

max(aggr(count(distinct {<['Semana Año']={'$(= [Semana Año])'}>} [ID CirugiaQ]), Fecha))&'-'&
min(aggr(count({<['Semana Año']={'$(= [Semana Año])'}, [ID CirugiaQ] -= {"=count(distinct [ID CirugiaQ])=0"}>} distinct [ID CirugiaQ]), Fecha))

[TcnCunha_M]

you have to add : DateFiel = , in you expression

i.E

max(aggr(count(distinct {< [Date]=, ['Semana Año']={'$(= [Semana Año])'}>} [ID CirugiaQ]), Fecha))&'-'&
min(aggr(count({<[Date]=,['Semana Año']={'$(= [Semana Año])'}, [ID CirugiaQ] -= {"=count(distinct [ID CirugiaQ])=0"}>} distinct [ID CirugiaQ]), Fecha))

[jyanik]

Hello! Thanks for your reply! 

Unfortunately, I tried it and it still shows me the calculation for the selected Date, instead of the Week Year, when a Date selection has been made.

If it helps, this is the resulting full code:

if(
    IsNull(GetFieldSelections(Fecha)),
    [Rango Cirugías Semanal],
    max(aggr(count(distinct {< [Fecha]=, ['Semana Año']={'$(= [Semana Año])'}>} [ID CirugiaQ]), Fecha))&'-'&
min(aggr(count({<[Fecha]=,['Semana Año']={'$(= [Semana Año])'}, [ID CirugiaQ] -= {"=count(distinct [ID CirugiaQ])=0"}>} distinct [ID CirugiaQ]), Fecha))
)

[marcus_sommer]

Each single part within your expression may need some set analysis to react appropriate to the selections respectively to ignore them. This means the inner- and outer aggregation as well as aggr() itself and also all nested calculations in the set analysis.

Further your attempt to exclude ZERO results may not always be working. Beside this it's writing an if-loop within a set analysis - so it might be more helpful to outsource this check as a boolean check against the other result.

Beside this you may simplify some things by querying the week directly, maybe with something:

... Week = p(Week) ...