Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to consider only records with max (Date), department wise.
ID | DEPT | POSITION_DT | Count( Distinct ID) |
100 | Computer | 1/21/2009 | 1 |
100 | Finance | 5/26/1992 | |
101 | Admin | 6/26/2016 | 1 |
101 | Education | 8/1/2013 | |
101 | Computer | 1/1/2007 |
Also, In chart I want to show the respective department only.
Kindly assist.
Regards,
Mahamed
Dimension: ID
Measures: FirstSortedValue(DEPT, -POSITION_DT) and Date(Max(POSITION_DT))
You could also do this by suppressing zero/null values and picking only the latest line in a measure if for some reason you don't want to use measures.
HI
But I want DEPT to be use as dimension because I want to show latest department wise count
The two halves of that statement don't seem to be related? It will show up as a measure. Do you have a specific reason you need it to be a dimension?
In Detail :
I have two dates ( CalDate and PosDate)
if I select my CalDate as 09/19/2018 then below records are populating which is correct:
ID DEPT DEPT_TYPE PosDate Count
100 Account AB 06/01/2021 1
100 Finance AB 03/29/2008 0
100 Finance AB 12/25/2011 0
100 Finance AB 01/01/2018 0
Now here you can see ID-100 was working in multiple DEPT with different PosDate and as per current logic
its calculating count as 1 for max of total PosDate.
Expetation is when user select Caldate as 09/19/2018 then during this period ID was working in Finanance DEPT and its max posDate is 01/01/2018 so this record needs to shown in count as 1.
expected result:
ID DEPT DEPT_TYPE PosDate Count
100 Account AB 06/01/2021 - <---- this record can be null or removed as Caldate is backdated date selected
100 Finance AB 03/29/2008 0
100 Finance AB 12/25/2011 0
100 Finance AB 01/01/2018 1 <--- this needs to shown only for backdated calDate with max posDate
How to achieve this in set analysis
No Solution???
Awaiting for solution