Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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

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

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

sunny_talwar

Sounds good

Not applicable

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