topic Re: Using Total Sum with a calculated dimension Qlik Sense in QlikView

Using Total Sum with a calculated dimension Qlik Sense

brentski — Sat, 16 Nov 2024 17:48:59 GMT

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!

Re: Using Total Sum with a calculated dimension Qlik Sense

Anil_Babu_Samineni — Tue, 17 Nov 2020 19:46:21 GMT

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

Re: Using Total Sum with a calculated dimension Qlik Sense

brentski — Tue, 17 Nov 2020 20:14:34 GMT

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

Re: Using Total Sum with a calculated dimension Qlik Sense

Anil_Babu_Samineni — Tue, 17 Nov 2020 20:17:30 GMT

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?

Re: Using Total Sum with a calculated dimension Qlik Sense

brentski — Tue, 17 Nov 2020 20:23:56 GMT

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.

Re: Using Total Sum with a calculated dimension Qlik Sense

Anil_Babu_Samineni — Tue, 17 Nov 2020 20:23:06 GMT

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

Re: Using Total Sum with a calculated dimension Qlik Sense

brentski — Tue, 17 Nov 2020 20:36:14 GMT

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'}

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

Re: Using Total Sum with a calculated dimension Qlik Sense

brentski — Tue, 17 Nov 2020 20:59:01 GMT

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