Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_id | month | marital_status |
1 | 31/01/2015 | s |
2 | 31/01/2015 | s |
3 | 31/01/2015 | m |
1 | 28/02/2015 | s |
2 | 28/02/2015 | s |
1 | 31/03/2015 | s |
2 | 31/03/2015 | m |
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
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
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
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
this will work to get the count of married ids are available in the data
Count({$<marital_status={"m"}>} card_id)
If I've well understood,
it's the last marital status (most recent)
with your formula you count all marital 'm' status
Olivier, thank you for help!
Regards,
Andrei