FirstSortedValue by Subtotal

[Naude716]

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 😊

[pallavi_96]

Hi,

Could you please explain how 425 will be the Total Current Cost for obesity?

[ali_hijazi]

Hello

in the FirstSortedValue function you need to sort rows by Claim ID in descending order
=Aggr(FirstSortedValue({<PERIOD= {'Current'}, TYPE={'1'} >} DIAGNOSIS, -CLAIM_ID),MEMBER_ID)
so for member ID = 1, the first sorted value Diagnosis ordered by CLAIM_ID is descending order is 13
i.e. Chron's

you wrote:
=Aggr(FirstSortedValue({<PERIOD= {'Current'}, TYPE={'1'} >} DIAGNOSIS, -COST),MEMBER_ID)
so this will return for Member ID =1, the first row ordered by COST in desceinding order i.e. 100 which means Diabetes

[Naude716]

Certainly, I should've been more specific. This column is the sum of all cost for this member in the 'Current' period.

sum(if(Period='Current', COST))

[ali_hijazi]

then you need to ignore the DIAGNOSIS dimension and aggr only by member using the "total"
so your expression would be 
sum(total<MEMBER_ID>{<PERIOD={[CURRENT]}>}COST)

[Naude716]

Thank you for your responses Ali_hijazi!

**this is in response to your initial reply**

Yes, sorting by CLAIM_ID descending would work for my example, but I need the TOP_DIAGNOSIS to return the highest DIAGNOSIS by total COST for that DIAGNOSIS. In actuality, the data will not have CLAIM_ID's in order like this.

For instance, say I have another row;

MEMBER_ID	CLAIM_ID	DIAGNOSIS	COST	PERIOD	TYPE
1	14	Infection	5	Current	1

If this row was added, then 'Infection' would be the 'TOP_DIAGNOSIS', when it's COST is only '5'.

Is there a way to SUM the DIAGNOSIS by member before sorting by cost, or is there perhaps a better way to do this other than using the FirstSortedValue() function?

[ali_hijazi]

Kindly show me in a table the expected result form the given data above

[Naude716]

MEMBER_ID	TOP_DIAGNOSIS	Total_Current_Cost
1	Chron's	250
2	Obesity	425

I would like Chron's to show for member 1 because it's total summed cost is 150 (out of the total CURRENT cost of 250 for Member 1), and likewise, obesity for member 2.

[ali_hijazi]

first expression for the diagnosis of the latest claim:
=Aggr(FirstSortedValue({<PERIOD= {'Current'}, TYPE={'1'} >} DIAGNOSIS, -CLAIM_ID),MEMBER_ID)

second expression for diagnosis with the highest cost:
=MAXSTRING(Aggr(FirstSortedValue({<PERIOD= {'Current'}, TYPE={'1'},CLAIM_ID={"=COST=MAX(COST)"}>} DIAGNOSIS, CLAIM_ID),MEMBER_ID,DIAGNOSIS))

third expression for the total:
SUM(TOTAL<MEMBER_ID>{<PERIOD= {'Current'}, TYPE={'1'} >}COST)

[Naude716]

Hi again. Thanks again for the help thus far. This doesn't appear to be the results I'm looking for..
The first expression isn't needed (diagnosis from the latest claim).
The second expression is not returning the diagnosis with the highest total cost.
Member 1 is showing Diabetes as the highest costing 'Current' diagnosis for this type '1' member. The total cost for Diabetes is 100, compared to the total cost for Chron's being 150 (75 + 75, or claim 12 + claim 13).