Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How can I get the sum of all cells in a column using a pivot table and a complex expression?

The expression is designed to adjust a physician's work relative value units (wRVUs) to account for money they brought in through contracts and the amount of time they spend on clinical tasks. Essentially, we want to know how many wRVUs a given physician would earn if they were working in the clinic full time and all year so we can help in reviewing their performance.

Sadly, this expression has to be in a pivot table so getting the total of this expression's results to appear in the subtotal rows is proving too difficult for me. I am going to try and write out the full expression. Keep in mind I am excluding all the set analysis, so please bear with me while i try to generalize it all:

//This variable is represents how much of the fiscal year has been completed.

Inverse_Year_Completion = 12/11

//This is the variable we are using to convert Contract money into wRVUs. I didn't create this, so I am not sure why this is used, but

// it is necessary to use.

Contract_Collections_wRVUs = SUM({contract} Charges) / ( SUM(TOTAL <[Department]> Charges) / SUM(TOTAL <[Department]> wRVU )

//Next I am going to explain some of the fields we are using in the metric.

Emp_FTE_Ratio = This is a modifier for a part-time-employee or people who ceased working before the year completed

Emp_Clinic_Time = This is a modifier for the percent of an employee's time spent in the clinic

//The following is the expression

$(Inverse_Year_Completion) * ( ( ( (SUM(wRVU) + SUM(Contract_Collections_wRVUs) ) / Emp_FTE_Ratio ) / Emp_Clinic_Time )

Thanks ahead of time to anyone who can help me resolve this issue!

Best Regards,

Mark H

3 Replies

Re: How can I get the sum of all cells in a column using a pivot table and a complex expression?

You didn't mention anything about your dimensions here? This is probably what you need

Sum(Aggr(

$(Inverse_Year_Completion) * ( ( ( (SUM(wRVU) + SUM(Contract_Collections_wRVUs) ) / Emp_FTE_Ratio ) / Emp_Clinic_Time )

,Dimension/s))

Replace Dimension/s with all the dimensions you use in your chart

Not applicable

Re: How can I get the sum of all cells in a column using a pivot table and a complex expression?

I've tried this solution a couple of times. My dimensions are Providers, Department Subsections, and Departments. So Departments are broken into subsections that Providers belong to.

I can't get it to work properly. It makes small changes to some of my numbers that end up putting my total ~2% off where I need it to be.

Re: How can I get the sum of all cells in a column using a pivot table and a complex expression?

So this isn't giving the right number?

Sum(Aggr(

$(Inverse_Year_Completion) * ( ( ( (SUM(wRVU) + SUM(Contract_Collections_wRVUs) ) / Emp_FTE_Ratio ) / Emp_Clinic_Time )

, Providers, [Department Subsections], Departments))

If not would you be able to share a sample or screenshots to show what you have and what exactly you are trying to achieve?