Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ogurchik
New Contributor II

Aggregation, select max date, count

Hello colleagues,

I'm newcomer in Qlik Sence and would appreciate your help.

I have data on employees married status (bellow) at the end of each month. If someone terminate working in company, his ID is not reflected at the end of following monthes.

   

card_idmonthmarital_status
131/01/2015s
231/01/2015s
331/01/2015m
128/02/2015s
228/02/2015s
131/03/2015s
231/03/2015m

I want to reflect in KPI chart number of married persons based on the last available information on each employee.

I suppose following algorithm:

1) Aggregate data on card_id field

2) Select record with max date

3) Count({$<marital_status={"m"}>} card_id)


Could you help me to translate this ides in lines of code?

Thanks in advance,

Andrei

1 Solution

Accepted Solutions
ogautier62
Valued Contributor II

Re: Aggregation, select max date, count

Hi

card_id as dimension

and expression last =  aggr(FirstSortedValue(marital_status,-month),card_id)

and to have total count in a text box :

=sum(if(aggr(FirstSortedValue(marital_status,-month),card_id)='m',1))

regards

5 Replies
marinadorcassio1
Contributor

Re: Aggregation, select max date, count

Hi Andrei,

I think, 2 solutions :

1) Create a seconde table with last record of each employee

T1_lastrec:

LOAD

     card_id,

     max(month) as last_month

From [source]

group by card_id;

inner join (T1_lastrec)

LOAD

     card_id,

     month as last_month,

     marital_status as last_marital_status

From [source];


Then Count({$<last_marital_status={"m"}>} card_id) should work.


2) Just adapt you measure like this : Count({$<month={$(=max(month))}, marital_status={"m"}>} card_id)


Hope it'll help,

Regards,

Marina

ogautier62
Valued Contributor II

Re: Aggregation, select max date, count

Hi

card_id as dimension

and expression last =  aggr(FirstSortedValue(marital_status,-month),card_id)

and to have total count in a text box :

=sum(if(aggr(FirstSortedValue(marital_status,-month),card_id)='m',1))

regards

manoj217
Contributor II

Re: Aggregation, select max date, count

this will work to get the count of married ids are available in the data

Count({$<marital_status={"m"}>} card_id)

ogautier62
Valued Contributor II

Re: Aggregation, select max date, count

If I've well understood,

it's the last marital status (most recent)

with your formula you count all marital 'm' status

ogurchik
New Contributor II

Re: Aggregation, select max date, count

Olivier, thank you for help!

Regards,

Andrei

Community Browser