Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
eloisateixeira
Contributor II
Contributor II

Count of the max records date

Hi,

I have a table like this:

MARITAL STATUSPATIENTS
Married196
Widowed75
Single70
Divorced14
Stable Union2
 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

1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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.

View solution in original post

6 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

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
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

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
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

Hey,

This solution worked, but look my example.

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

Why?