Empty values while using [Avg] in combination with [Aggr]

[sgrobben]

I have a table with, for example, the following data:

Research	Agenda	StartDate	AdmissionDays
Echo FB	E1	17-03-2021	12
Echo FB	E1	17-07-2021	25
Echo Abdo	E1	18-05-2021	0
Echo Abdo	E1	25-06-2021	25
Echo Neck	E1	13-06-2021	25
Echo Mammo	E1	18-07-2021	0
Echo MSK	E1	22-07-2021	71
Arthro	DL1	23-06-2021	36
Oeso	DL1	23-07-2021	28

I only want to count the records where the startdate is the same as the maximum date of that [Research].
I therefore use the following set-expression:

if(Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays])=-1,0,
Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays]))

Which gives me the following outcome:

Research	Agenda	StartDate	AdmissionDays
Echo FB	E1	17-07-2021	25
Echo Abdo	E1	25-06-2021	25
Echo Neck	E1	13-06-2021	25
Echo Mammo	E1	18-07-2021	0
Echo MSK	E1	22-07-2021	71
Arthro	DL1	23-06-2021	36
Oeso	DL1	23-07-2021	28

When i want to get the average number per [Agenda] i do the following set-expression:

Avg(Aggr(if(Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays])=-1,0,
Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays])),[Research]))

I put these values in a seperate table like this:

Agenda	Average
E1	-
DL1	32

It shows the correct average for agenda [DL1], but gives me an empty answer for agenda [E1]. If I however select only agenda [E1] and submit, it all of a sudden gives me the right answer, like this:

Agenda	Average
E1	29

How is it possible that when it shows me all agenda's, one of the agenda's is not shown correctly, while when I select it, it gives me the right calculation?

[dwforest · Accepted solution]

Think you should add [Agenda] to the agrr

Avg(Aggr(if(Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays])=-1,0,
Min({1}{<[StartDate] = {"$(=Date(Max([StartDate]), 'DD-MM-YYYY'))"}>}[AdmissionDays])),[Research],[Agenda]))

[NageshSG]

Please try the following, seems to work for me.

Dimension: Agenda

Measure: avg({<StartDate={$(=chr(39)&concat(aggr( max(StartDate),Research),chr(39)&chr(44)&chr(39))&chr(39))}>}AdmissionDays)

If you want to use a Pivot table with Agenda and Research as dimensions:

Measure:  avg({<StartDate={$(=chr(39)&concat(aggr( max(StartDate),Research),chr(39)&chr(44)&chr(39))&chr(39))}>} TOTAL<Agenda> AdmissionDays)

[NageshSG]

Hi @dwforest , I tried this solution but unable to get expected results! Not sure where I am making a mistake.  Please see the attached qvw file.

The idea of proposing the alternate solution is when we use "If" conditions in expressions against a very large data set, it could impact the memory usage.

Thanks for your time.