Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ExpenseType | AcctDetail |
---|---|
Revenues | Sales Revenue 1 |
Revenues | Sales Revenue 2 |
Direct Expenses | COGS |
Direct Expenses | Direct Labor |
Direct Expenses | Direct Postage |
Direct Expenses | Repair & 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!
You would need to use the Total Qualifier here:
=Sum(TOTAL {$<ExpenseType={'Revenues'}, Year={2015} >} [# Amount])
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".
Not sure what you mean, would you be able to share a sample?
Sure thing. I'm tied up now, will add a sample file later tonight. Thanks
Sounds good
Brett are you still tied up, its been a year and half