Count of the max records date

[eloisateixeira]

Hi,

I have a table like this:

MARITAL STATUS	PATIENTS
Married	196
Widowed	75
Single	70
Divorced	14
Stable Union	2
	357

I would like to count how many ID_USERS are in each marital status, but annually the patients are monitored and the question about marital status is asked again, so the status can change. I need an expression that count the max date record for my patient not to duplicate. 

ps: i don't use the date in my dimension

[Stoyan_Terziev]

Hi Eloisa,

Try this:

Sum(
	Aggr(
	If(Max(Total <ID_USERS> Date)=Date
		, 1, 0
	)
	, ID_USERS, Date
		)
	)

This should be dimension agnostic.

Kind regards,

S.T.

[eloisateixeira]

Thank u, but this expression didn't work. 

I try: 

COUNT(Aggr(
If(Max(Total <ID_USERS> DATE_WORK)=DATA_WORK
, 1, 0
)
, ID_USERS, DATA_WORK
)
)

DATE_WORK is the date that the questionnaire was applied

[Stoyan_Terziev]

Hi,

Make sure to use Sum instead of Count. Due to the particularity of the case, COUNT cannot be used.

I noticed the field names weren't consistent (DATA_WORK vs DATE_WORK). So here is the updated expression:

Sum(Aggr(
If(Max(Total <ID_USERS> DATE_WORK)=DATE_WORK
, 1, 0
)
, ID_USERS, DATE_WORK
)
)

Let me know if that works.

Kind regards,

S.T.

[eloisateixeira]

Didn't work. Look my dimension:

=if(QUESTION = 'P359 - Marital Status' ,if(WORK = '3 - Personal Information', ANSWER))

And in expression i put your expression, but brought the number "0".

[Stoyan_Terziev · Accepted solution]

Hey,

Keep in mind the solution I propose will only work in a generic situation. You may need to customize it to your data model and specific chart properties.

I'd suggest you try modifying your Dimension to be simply [ANSWER] instead of this if-condition and fix the results with a set expression in the metric like so:

Sum({<QUESTION = {'P359 - Marital Status'}, WORK = {'3 - Personal Information'}>} Aggr(
If(Max(Total <ID_USERS> DATE_WORK)=DATE_WORK
, 1, 0
)
, ID_USERS, DATE_WORK
)
)

Let me know if that works.

Kind regards,

S.T.

[eloisateixeira]

Hey,

This solution worked, but look my example.

When select the ANSWER in the list, my TOTAL change.

Why?