Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
brentski
Contributor III
Contributor III

Calculated Dimensions for a selected population

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

 

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

3 Replies
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
Contributor III
Contributor III
Author

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

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