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 😊

[Naude716]

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

[ali_hijazi]

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;