Max date set analysis doesn't work.

[marcel_olmo]

Hey guys, first of all, I want to tell that I've been trying all the post refering "max date set analysis", but it doesn't work for me, and I don't know why.

Here I have a pivot table that shows the Medical History of a patient, the ID Episode (a patient has 1 Medical History but could have N Episodes), the date of the ending of the episode, and the number of treatment the patient receives.

By now, if I want to calculate the treatments of the Medical History 10267, the result would be 17 + 21 = 38. And my desired result is 21 (the last one).

Moreover, I need to consider that the last episode could not be finished, so the date is null, and I need to take the last finished episode.

For example, the Medical History 10999 has 2 episodes, one finished and the other not, if I count it now, the result would be 22 + 23 = 45. And my desired result is 22, because is the number of treatments of the last finished episode.

Here's my expression to calculate the number of treatments, what I'm doing is to filter by some kind of pacient benefits "UsuariPrestacio", and the date values between my selected dates of the application :

=count({$< UsuariPrestacio = {'CPL', 'CNV' ,'ME PSICO', 'LLE'} >} distinct

IF( (DataAltaNum >= vEstadaMinData AND DataAltaNum <= vEstadaMaxData) OR

(DataIngresNum <= vEstadaMaxData AND (DataAltaNum = 0 OR DataAltaNum > vEstadaMaxData)), IDTractament ))

What I've added to fix it is the max(date), in this case "DataAltaNum" of the last episode, but it doesn't work. I do exactly this :

=count({$< UsuariPrestacio = {'CPL', 'CNV' ,'ME PSICO', 'LLE'} , DataAltaNum = {'$(=Max(DataAltaNum))'} >} distinct

IF( (DataAltaNum >= vEstadaMinData AND DataAltaNum <= vEstadaMaxData) OR

(DataIngresNum <= vEstadaMaxData AND (DataAltaNum = 0 OR DataAltaNum > vEstadaMaxData)), IDTractament ))

Anybody knows what I'm doing wrong?

Any clue would be appreciated.

Many thanks by advance!!!

Regards

[#2]

When you use Max(DataAltaNum) inside a dollar sign expansion in your final expression, it is not evaluating the Max for that record, but the Max for your entire data set.

I think you would need something like:

=count({$< UsuariPrestacio = {'CPL', 'CNV' ,'ME PSICO', 'LLE'} ,
DataAltaNum = {'$(=Aggr(Max(DataAltaNum)), MedicalHistory, Episode)'} >} distinct ...

Dollar Sign Expansions are evaluated for the entire data set. They are not evaluated record by record, which is probably why they are so fast. This will get the Max for every combination on MedicalHistory & Episode (guessed at those field names), which seems to be what you are going for.

EDIT: It looks like I was confused. I don't think the Aggr works in the dollar sign expansion like this. You may need to add this clause in an if statement, rather than Set Analysis.

[marcel_olmo]

Thank you very much NMiller to answer me so quickly.

I'm afraid your solution still doesn't work for me. I did it as you wrote (changing the name of the variables for the real ones), but it doesn't work.

Is there any other possibility?

Thanks anyway!!!

[#4]

Try something like this (ifs instead of Set Analysis):

=count({$< UsuariPrestacio = {'CPL', 'CNV' ,'ME PSICO', 'LLE'} distinct
IF( (DataAltaNum >= vEstadaMinData AND DataAltaNum <= vEstadaMaxData) OR
(DataIngresNum <= vEstadaMaxData AND (DataAltaNum = 0 OR DataAltaNum > vEstadaMaxData))
AND DataAltaNum = Max(TOTAL <MedicalHistory, Episode> DataAltaNum), IDTractament ))

That should require one of your original ifs to be true and require that the DataAltaNum field match the Maximum DatAltaNum per MedicalHistory and Episode combination.

[marcel_olmo · Accepted solution]

Thank you NMiller again. You gettin very closer. But the point is that you inspire me to get the right solution.

This is what I get, more or less is my desired result, and a valid solution :

I've realized that I cannot get the max value of a date, it has to be a number. so to calculate the "Max Number date" I do as a expression :

Max Number date : Max(TOTAL <HC> DataAltaNum)

And to calculate the Number of treatments of the last episode, I check with an IF, if it's the last date of the episode, I put the counter of episodes, otherwise, I put a 0, so when I make the sum of all the values, everything is gonna be well calculated.

This is my solution :

Nº of treatments : = if ( DataAltaNum = Max(TOTAL <HC> DataAltaNum),

count({$< UsuariPrestacio = {'CPL', 'CNV' ,'ME PSICO', 'LLE'} >}

distinct IF( ( (DataAltaNum >= vEstadaMinData AND DataAltaNum <= vEstadaMaxData) OR

(DataIngresNum <= vEstadaMaxData AND (DataAltaNum = 0 OR DataAltaNum > vEstadaMaxData)) ) , IDTractament )),0 )

Thanks NMiller for helping me.

See you around!

Regards.