topic Re: FirstSortedValue by Subtotal in App Development

FirstSortedValue by Subtotal

Naude716 — Tue, 19 Mar 2024 21:45:52 GMT

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 😊

Re: FirstSortedValue by Subtotal

pallavi_96 — Thu, 21 Mar 2024 06:16:37 GMT

Hi,

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

Re: FirstSortedValue by Subtotal

ali_hijazi — Thu, 21 Mar 2024 07:20:25 GMT

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

Re: FirstSortedValue by Subtotal

Naude716 — Thu, 21 Mar 2024 14:02:40 GMT

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))

Re: FirstSortedValue by Subtotal

ali_hijazi — Thu, 21 Mar 2024 13:52:35 GMT

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)

Re: FirstSortedValue by Subtotal

Naude716 — Thu, 21 Mar 2024 15:09:59 GMT

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?

Re: FirstSortedValue by Subtotal

ali_hijazi — Thu, 21 Mar 2024 15:21:56 GMT

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

Re: FirstSortedValue by Subtotal

Naude716 — Thu, 21 Mar 2024 15:38:16 GMT

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.

Re: FirstSortedValue by Subtotal

ali_hijazi — Thu, 21 Mar 2024 15:49:46 GMT

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)

Re: FirstSortedValue by Subtotal

Naude716 — Thu, 21 Mar 2024 18:03:03 GMT

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).

Re: FirstSortedValue by Subtotal

Naude716 — Thu, 21 Mar 2024 18:49:52 GMT

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

Re: FirstSortedValue by Subtotal

ali_hijazi — Fri, 22 Mar 2024 11:24:16 GMT

hello

to get the diagnosis with max cost as a sum this would solve it:

maxstring

(

{

PERIOD= {[Current]}

, TYPE={[1]}

,MEMBER_DIAGNOSIS_ID={"=min(aggr(rank(sum({<PERIOD= {[Current]}, TYPE={[1]}>}COST)),MEMBER_ID,DIAGNOSIS))=1"}

}

DIAGNOSIS

)

{[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;