Calculated Dimensions for a selected population

[brentski]

I like to start off by saying I am in a hosted environment, so creating variables or flags are not an option for me.

What I am trying to do, created a cohort population.  I want to compare those who have something to those that don't.  For instances, I want to show how much people who eat ice cream spend at the movies vs those that don't eat ice cream.  Simple right two populations, those that eat ice cream and those that don't summed up by money.  There is a dimension that has all the things that people eat, so each person can eat more than one thing.  I need to cycle through and find all the MemberIDs belonging to the ice cream eaters and have that as a cohort.

That was just the example, but my real question is for people who have diabetes.  I have a dimension called Morbidity and it is defined at the member level and each person can have "n" number of morbidities.  Because Morbidity is a member level attribute, I want those [Member ID]s that have Diabetes compared to those that don't.

I am stuck at multiple points on this.

1. =AGGR(If(sum( {$ <[Member ID]={"=sum({<[Morbidity]={Diabetes}>} [Member Months Fraction])>0"} >} [Member Months Fraction])>0,[Member State]), [Member ID])--This only returns diabetics and not the non-diabetics

This works for the total number of dollars by the state the member lives in for diabetics, the non-diabetics returns "-" (aka Null).  I want it to say "other" or "non-diabetics" or anything, but no matter what I put after the IF comma, it returns null.  How do I get it to show "other" instead of "-"?

=AGGR(If(count( {$ <[Member ID]={"=sum({<[Morbidity]={Diabetes}>} [Member Months Fraction])>0"} >} [Member ID])>0,[Member State]), [Member ID])	Sum([Amount Paid])
-	$84,969,149.17
MI	$44,821,817.37
GA	$33,048.00
FL	$30,306.81
DE	$22,209.90
TN	$8,772.28
SC	$5,906.32
AZ	$3,828.48
OH	$3,623.76
AL	$2,202.44
MD	$984.00
MS	$927.51

2. If I use the same statement but do not use a member dimension I do not get the correct results.

=AGGR(If(sum( {$ <[Member ID]={"=sum({<[Morbidity]={Diabetes}>} [Member Months Fraction])>0"} >} [Member Months Fraction])>0,[Service Category]), [Member ID])--$583k.  

[Service Category] is a claim level field and each claim is categorized into one of 3 categories.  Inpatient, Outpatient, or Professional.  Not all diabetics have claims but all claims have [Member ID] and all claims fall into one of the 3 categories, there are no claims that would show up as "-" (aka Null). What am I missing for this to give me all the claims for members that are diabetics?

=AGGR(If(sum( {$ <[Member ID]={"=sum({<[Morbidity]={Diabetes}>} [Member Months Fraction])>0"} >} [Member Months Fraction])>0,[Service Category], "other"), [Member ID])	Sum([Amount Paid])
-	$129,319,682.25
Professional	$582,917.39
Outpatient	$176.40

[rwunderlich]

For defining cohorts, I generally use the P() and E() set functions. 

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

Sum({<[Member ID]=P({<[Morbidity]={'Diabetes'}>})>}[Amount Paid])  // Amount paid by Members with Diabetes

Sum({<[Member ID]=E({<[Morbidity]={'Diabetes'}>})>}[Amount Paid])  // Amount paid by Members without Diabetes

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[brentski]

Thanks for the suggestion on how to write a calculated measure to get the population.  I tend to forget about the P and E conditions.  I have tried to create a calculated dimension using your method and have been very unsuccessful in doing that.  I really want to be able to create a sheet where I have several different cuts of diabetics vs non diabetics and how they utilize care differentially.  So, using the calculated dimension seems the way to go for me.

Any other thoughts are appreciated.  

[rwunderlich · Accepted solution]

I generally find that a Measure is the way to go, but there are times when a calculated dimension is called for. You can use the same sets in a calculated dimension. Use an Only() wrapped in an Aggr(). For example:

Aggr(Only({<SalesPerson=P({<Country={"s*"}>})>}SalesPerson), SalesPerson) 

Avoid using if() 😉

-Rob