How to find latest status for Cases

[manojkulkarni]

Hi Friends,

I have set of case IDs along with status per day in which i need to identify the distinct cases id with referred status only. The cases referred and closed on the same day should not be counted. Below is the sample data along with expected output.

Appreciate any suggestion

ID	Status	Date
K1	Referred	02-11-2015
K1	Closed	02-11-2015
K2	Referred	02-11-2015
K3	Referred	02-11-2015
K3	Referred	02-11-2015
K4	Closed	02-11-2015
K5	Open	02-11-2015

Expected Output

ID	Status
K2	Referred
K3	Referred

[its_anandrjs]

Try this

MainTable:

Load * Inline

[

ID, Status, Date

K1, Referred, 02-11-2015

K1, Closed, 02-11-2015

K2, Referred, 02-11-2015

K3, Referred, 02-11-2015

K3, Referred, 02-11-2015

K4, Closed, 02-11-2015

K5, Open, 02-11-2015

];

Final:

Load

ID,Date,

If(ID = Previous(ID) and Status = Previous(Status), Status) as Status

Resident Where Status = 'Referred';

//This Drop table is optional

Drop Table MainTable;

[Kushal_Chawda]

Data:

LOAD ID,

           Status,

           Date

FROM table;

left join(Data)

LOAD ID,

           Cont(ID) as CntID

resident Data

group by ID;

Now you can use below expression in chart with ID as dimension

=only({<CntID={1},status={'Referred'}>} Status)

[Kushal_Chawda]

or on front end

Dimension:

ID

Expression:

= only ({<ID={"=count(ID)=1"},Status={'Referred'}>} Status)

[antoniotiman]

Hi Manoj,

try this

Only({<ID=P({<Status={'Referred'}>})>} Status)

Regards,

Antonio

[rohitraut]

Hello Manoj,

Try this,

In Calculated Dimension,

=Aggr(if(Status <> 'Closed'and Status <>'Open' and Status='Referred',ID),ID)

[manojkulkarni]

Thanks Antonio, This worked. How can we get the count of referred cases ?

[antoniotiman · Accepted solution]

In Text Box

=Count({<ID=P({<Status={'Referred'}>})>} DISTINCT Status)

[effinty2112]

Hi Manoj,

Another suggestion:

ID	if(aggr(Concat(DISTINCT Status,','),ID,Date)= 'Referred',Status)
K2	Referred
K3	Referred

Cheers

Andrew

[Kushal_Chawda]

or try this

only ({<ID={"=count({<Status={'Referred'}>}ID)>=1"}>} Status)