Using Total Sum with a calculated dimension Qlik Sense

[brentski]

Good Afternoon!  I really enjoy having this forum and looking through all of the questions throughout time.

In the picture, I am trying to use Member Months Fraction in a calculation at the Total level in each of the buckets.  The table on the left has Member Months Fraction in each of the cells.  I want that number to be 667,190 in every cell. When I use Total like on the table on the right, all of the member months fall into the Null() number of visits.

How do I use that 667k total across all cells?

The dimension that I am using takes a field called visits and counts up the number of visits someone has and buckets them into the ranges.  I am using set analysis in that measure because I want specific types of visits. 

I do not have access to the data model and only have access to the front end.    

I appreciate anyone's thoughts!

[Anil_Babu_Samineni]

First thing you remove Aggr() and then you should use 

Sum(TOTAL <FieldName> Measure)

If you use Sum(TOTAL {<FieldName>} Measure) It always ignores the field since identifier by analysis

[brentski]

I gave it a shot and it doesn't seem to respect the Total function.  I got the same results as before.  I wonder if the calculated dimension is messing with the total somehow.

To say it in words, I want all of the member months associated with the year to be in every visit cohort.  My goal is to calculated a visits per 1,000 which is the number of visits/total member months*12000

[Anil_Babu_Samineni]

Is there a way that you can attach, I think since you are using you must use Below() to achieve but If you can share sample application may be I can quick check?

[brentski]

Here is the same table and just using Sum(Total Member Months).  It places all 3 years of member months in each cell.  If I change the expression to Sum(Total <Incurred Year> Member Months) it puts all of the numbers just like in the original image where they are the fraction of the 670k.

[Anil_Babu_Samineni]

I meant to do workshop in your application not the same image ..

[brentski]

sorry, I cannot post the application.  We are in a hosted and restricted environment which is often why I find myself in these predicaments.  

Here is the Calculated dimension(maybe that will help):

=AGGR(if(sum({$<[Service Category Code]={PR}
, [Place of Service]={'11 Office','22 Outpatient Hospital - On Campus'}

, [Plan Service Provider Specialty Code]-={'01', '08', '11', '16', '23', '37', '96', 'AN', 'D8', 'NP'}
, [Procedure Code (CPT/HCPCS)]={'99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391', '99392', '99393', '99394', '99395', '99396', '99397', '99401', '99402', '99406', '99407', '99408', 'S0610', 'S0612', 'S0613', '99241', '99242', '99243','99244','99245'}

>}Visits)>4,'5+',if(sum({$<[Service Category Code]={PR}
, [Place of Service]={'11 Office','22 Outpatient Hospital - On Campus'}

, [Plan Service Provider Specialty Code]-={'01', '08', '11', '16', '23', '37', '96', 'AN', 'D8', 'NP'}
, [Procedure Code (CPT/HCPCS)]={'99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391', '99392', '99393', '99394', '99395', '99396', '99397', '99401', '99402', '99406', '99407', '99408', 'S0610', 'S0612', 'S0613', '99241', '99242', '99243','99244','99245'}

>}Visits)=1,'1 Visit','2-4 Visits') ), [Incurred Year],[Member ID])

[brentski · Accepted solution]

Anil,

Thank you so much for your help.  I have found a solution based on an old old thread.  It is not elegant but it works.

rangesum(first(sum(total<[Incurred Year]>[Member Months Fraction]),1,NoOfColumns()))