Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I'm relatively new to QlikSense. I have what I hope to be a pretty simple question.
I would like to make a table for the top DIAGNOSIS a member has by COST.
My data-set looks like this:
MEMBER_ID | CLAIM_ID | DIAGNOSIS | COST | PERIOD | TYPE |
1 | 11 | Diabetes | 100 | Current | 1 |
1 | 12 | Chron's | 75 | Current | 1 |
1 | 13 | Chron's | 75 | Current | 1 |
2 | 31 | Obesity | 100 | Current | 1 |
2 | 32 | Obesity | 200 | Current | 1 |
2 | 33 | Diabetes | 125 | Current | 1 |
2 | 34 | Bronchitis | 300 | Prior | 1 |
3 | 41 | Cataracts | 5 | Current | 2 |
More specifically, I want to return the top DIAGNOSIS summed by COST , for TYPE '1' members in the 'Current' PERIOD.
That being said, the resulting table I would like to create would look like this:
MEMBER_ID | TOP_DIAGNOSIS | Total_Current_Cost |
1 | Chron's | 250 |
2 | Obesity | 425 |
So far I have this formula which almost works for the TOP_DIAGNOSIS column.
=Aggr(FirstSortedValue({<PERIOD= {'Current'}, TYPE={'1'} >} DIAGNOSIS, -COST),MEMBER_ID)
This formula returns the following column for this table:
MEMBER_ID | TOP_DIAGNOSIS | Total_Current_Cost |
1 | Diabetes | 250 |
2 | Obesity | 425 |
Thus, I believe my formula is looking claim by claim (row by row) showing the highest cost by diagnosis, and not summing up by diagnosis first.
TLDR:
How can I subtotal 'DIAGNOSIS' by 'COST' before returning the top 'DIAGNOSIS' by 'COST'?
Thanks in advance 😊
MEMBER_ID | CLAIM_ID | DIAGNOSIS | COST | PERIOD | CATG |
1 | 987 | Diabetes | 100 | Current | 1 |
1 | 172 | Chron's | 75 | Current | 1 |
1 | 737 | Chron's | 85 | Current | 1 |
1 | 146 | Infection | 5 | Current | 1 |
2 | 314 | Obesity | 100 | Current | 1 |
2 | 932 | Obesity | 200 | Current | 1 |
2 | 363 | Diabetes | 125 | Current | 1 |
2 | 342 | Bronchitis | 300 | Prior | 1 |
3 | 415 | Cataracts | 5 | Current | 2 |
This example data-set might be a little better to work with. I scrambled the CLAIM_ID numbers a bit, added an additional claim and changed the "TYPE" column to be called "CATG" instead (I think TYPE is a reserved name in Qlik).
The intended resulting table is the same except for the total current cost.
MEMBER_ID | TOP_COSTING_DIAGNOSIS | Total_Current_Cost_Overall |
1 | Chron's | 265 |
2 | Obesity | 425 |
hello
to get the diagnosis with max cost as a sum this would solve it:
{[1]}>}COST)),MEMBER_ID,DIAGNOSIS))=1"}>}DIAGNOSIS)
where MEMBER_DIAGNOSIS_ID is a new field created as follows:
AutoNumberHash256(MEMBER_ID, DIAGNOSIS) AS MEMBER_DIAGNOSIS_ID;