topic Re: Count of the max records date in QlikView

Count of the max records date

eloisateixeira — Mon, 18 Nov 2019 19:36:17 GMT

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

Re: Count of the max records date

Stoyan_Terziev — Mon, 18 Nov 2019 20:12:20 GMT

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.

Re: Count of the max records date

eloisateixeira — Mon, 18 Nov 2019 20:33:26 GMT

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

Re: Count of the max records date

Stoyan_Terziev — Mon, 18 Nov 2019 20:45:27 GMT

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.

Re: Count of the max records date

eloisateixeira — Mon, 18 Nov 2019 20:58:27 GMT

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".

Re: Count of the max records date

Stoyan_Terziev — Mon, 18 Nov 2019 21:08:59 GMT

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.

Re: Count of the max records date

eloisateixeira — Mon, 25 Nov 2019 20:48:23 GMT

Hey,

This solution worked, but look my example.

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

Why?