Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brentski
Contributor III
Contributor III

Using Total Sum with a calculated dimension Qlik Sense

Total Sum with Calculated Dimension.PNG

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!

 

Labels (2)
1 Solution

Accepted Solutions
brentski
Contributor III
Contributor III
Author

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

 

View solution in original post

7 Replies
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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
brentski
Contributor III
Contributor III
Author

Total Sum with Calculated Dimension No AGGR.PNG

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
brentski
Contributor III
Contributor III
Author

Total Sum with Calculated Dimension no break.PNG

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 ..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
brentski
Contributor III
Contributor III
Author

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

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