Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Force calculation regardless of current dimension

I'm attempting to calculate % margin for a number of gross margin items, and I need to sum the total revenues regardless of dimension, so that it can be used in the denominator. The data is structured like this:

ExpenseTypeAcctDetail
RevenuesSales Revenue 1
RevenuesSales Revenue 2
Direct ExpensesCOGS
Direct ExpensesDirect Labor
Direct ExpensesDirect Postage
Direct ExpensesRepair & Maintenance

The chart has AcctDetail as the dimension. I want each of the Direct Expenses to be shown in the bar chart, but I need to divide them by total revenues. Unfortunately Qlikview is not summing the revenues across the dimensions.

This expression works to show the $ value for each of the AcctDetail items that are not a revenue:

Sum({$< ExpenseType={'Direct Expenses'}, Year={2015} >} [# Amount])        // Expression 1

I've been attempting to create a set expression that would sum the Revenues regardless of the dimension, without success. I need a set expression because I need to be able to control the calculation based on the year. This doesn't work:

=Sum({$<ExpenseType={'Revenues'}, Year={2015} >} [# Amount])                // Expression 2 - not working

Qlikview seems to be filtering out the result because it doesn't match the dimension (AcctDetail). I've played around with various set expressions and can't seem to get anywhere. Ultimately I just need the total value of Revenues for all of 2015, which should be the same amount for each of the dimensions, so that I can add it as the denominator to expression 1 and convert the output there into a percentage of total revenues.

Appreciate any help on this!

6 Replies
sunny_talwar

You would need to use the Total Qualifier here:

=Sum(TOTAL {$<ExpenseType={'Revenues'}, Year={2015} >} [# Amount])    

Not applicable
Author

Thanks Sunny. Should have noted in the question that I've tried that. However, the data doesn't calculate. If I attempt to just calculate the dimension AcctDetail, with that expression, I get "No data to display".

sunny_talwar

Not sure what you mean, would you be able to share a sample?

Not applicable
Author

Sure thing. I'm tied up now, will add a sample file later tonight. Thanks

sunny_talwar

Sounds good

Not applicable
Author

Brett are you still tied up, its been a year and half