Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have what I hope to be a simple question.
Say I have a data table;
MEMBER_ID | CLAIM_ID | DIAGNOSIS | COST |
1 | 154 | Diabetes | 100 |
1 | 762 | Infection | 150 |
1 | 612 | Diabetes | 120 |
2 | 999 | Flu | 50 |
2 | 211 | Cataracts | 150 |
2 | 333 | Diabetes | 50 |
-and I wish to get the resulting table;
MEMBER_ID | TOP_COSTING_DIAGNOSIS | TOTAL_COST_OVERALL |
1 | Diabetes | 370 |
2 | Cataracts | 250 |
I'm having trouble getting the second column to return the highest costing diagnosis.
In this example, member 1 is showing diabetes because this member has two claims that total to 220 (100+120), which is higher than this member's other claim for infection (170).
I tried posting something a similar question a day ago that I believe just had too much going on.
I hope this is clearer on what I'm trying to ask for.
Hello,
Just add in your table :
MEMBER_ID (Dimension)
DIAGNOSIS (Dimension)
SUM(COST) (Measure)
And sort it by measure SUM(COST) desc .
Best Regards,