Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Force calculation regardless of current dimension

You would need to use the Total Qualifier here:

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

Highlighted
Not applicable

Re: Force calculation regardless of current dimension

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

Highlighted

Re: Force calculation regardless of current dimension

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

Highlighted
Not applicable

Re: Force calculation regardless of current dimension

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

Highlighted

Re: Force calculation regardless of current dimension

Sounds good

Highlighted
Not applicable

Re: Force calculation regardless of current dimension

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