Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Which takes precedence - TOTAL qualifier or Set Analysis?

I have a pivot table, dimension "company", and expressions for total sales for each month, a year total, and the % of each company's sales for the total. The problem is computing the percentage, both on the company level, and at the chart level. For all my calculations, I don't want to include the TAX GROUP, which is in a different business but whose records are in my table (and which I can't exclude at the script level, as mgmt. wants to see their report in a separate tab).

Here's the formula: (note: Year Sales is just the sum of the monthly sales by company, and it computes correctly)

% of sales = [Year Sales]/sum({<company-={'TAX GROUP'}>} TOTAL if(Class='Broker',extprice))

If I include TOTAL in the expression, my denominator DOES include the Tax Group. However, if I exclude it, instead of the chart

showing (e.g.) if company XYZ has $10,000 out of $1 million total sales "1%", it shows "100%", and it does that for every company,

when what we want to see is

ABC   $35,000   3.5%

DEF   $52,000   5.2%

XYZ   $10,000    1.0%, etc.

What I want in the denominator is the total sales for ALL companies EXCEPT Tax Group. How would I show that?

0 Replies