Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need a help to get highest(more recent) value from the dataset having null values.
Suppose, below is my inline table :
LOAD * INLINE [
ss_patient_id, EncounterDate, Group
1,6/5/2015, A
1,6/3/2015,B
1,5/19/2015,B
1,1/1/2015,C
2,6/5/2015, A
2,6/3/2015,B
2,3/13/2015,C
2,1/1/2015,B
3,6/5/2015, B
3,5/3/2015,B
3,4/13/2015,B
3,3/1/2015,A
3,2/13/2015,A
3,1/1/2015,A
1,6/5/2015,-
1,6/3/2015,-
1,5/19/2015,-
1,1/1/2015,-
];
Expected output is need to show recent or latest Group for every time period (Year, Quarter, Month).
Example: For patient id '1' above, for quarter 1, 'C' group should be displayed and for Quarter 2, 'A' should be displayed in 'Bar Chart.
Have used below expression in calculated dimension for Bar chart:
= aggr( nodistinct FirstSortedValue(Group,-EncounterDate & ss_patient_id),[New Group],ss_patient_id)
But this not seems to be working if we have null values in 'Group' column.
Need to handle null values in above expression. Find attached sample for more details.
Those are not really null in your sample app but '-'. May be you can try like:
aggr( nodistinct FirstSortedValue({<Group-={'-'}>}Group,-EncounterDate & ss_patient_id),[New Group],ss_patient_id)
Thanks Tresesco,
We can also avoid null values using "{<Group = {"*"}>}" in above expression. Is that right?
We can. However, as I mentioned ealier, yours in sample are not really nulls. They are hyphen character, so they would be included in '*' set analysis which you probably don't want.