Which takes precedence - TOTAL qualifier or Set Analysis?

Tue, 06 May 2014 14:59:39 GMT

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?

topic Which takes precedence - TOTAL qualifier or Set Analysis? in QlikView

Which takes precedence - TOTAL qualifier or Set Analysis?