topic Set Analysis : avoiding specific dimension in QlikView

Set Analysis : avoiding specific dimension

Mon, 18 Jan 2010 18:04:27 GMT

Hi again !

I have a question about set analysis.

Here is a simple project :

[CODE]

LOAD * INLINE [
Continent, Country, City, Sales, Year
America, USA, New York, 100, 2009
America, USA, New York, 10, 2010
America, USA, Los Angeles, 80, 2009
America, Canada, Toronto, 20, 2009
Europe, France, Paris, 25, 2009
Europe, France, Lyon, 15, 2009
Europe, UK, London, 25, 2009
Asia, Japan, Tokyo, 20, 2009
];

[/CODE]

I want to create a selection on the "Year", and cross table that says :
- the sum of sales by city --> =sum({$} Sales)
- the sum of the sales by country --> ???
- the market share --> =[SUM / City] * 100 / [SUM / Country]

With the set analysis, I understand that :
- "$" filters with the current dimensions AND the current selection
- "1" filters with the current dimensions but NOT the current selection
- "$<Year=>" filters with the current dimensions AND the current selection, but NOT the "Year" selection.
- "1 ... total" filters without the dimensions and without the selection

I can avoid 1 or many selection (with "{$<[Year]=>}"). I can consider a selection (with "{1<[Year]=P({$} [Year])>}"). I can avoid all dimensions (with "{$} total").

How can I avoid / consider a specific dimension ? (the "sum of the sales by country" in my exemple wants to filter by the dimensions, except the "City" one)

I dont want to pre-calculate the "sum by country" in my script, because in my real case, it adds circle references.

Set Analysis : avoiding specific dimension

Mon, 18 Jan 2010 18:27:44 GMT

For Sales by Country, try (you don't need {$}, since that is the default):

Sum(Total <Country> Sales)

EDIT: I attached a file with the expression for Total by Country. I didn't look at the third expression, but since that it based on the other two, I figured that would work.

Set Analysis : avoiding specific dimension

Mon, 18 Jan 2010 18:42:50 GMT

Hmmm...

Good news : it works ! Thank you !

Bad news : I don't understand what "Total <field>" does exactly. Could you explain me how this key word works ?

I've read a lot on the "set analysis" in the QV manual, but the key word "total" is not explained with it's parameters.

I assume it's like a "group by" ?

Set Analysis : avoiding specific dimension

Mon, 18 Jan 2010 18:51:02 GMT

I actually got that syntax on this forum. My understanding is it totals by that dimension. I think "like a group by" is accurate. You can use multiple dimensions separated by a comma.

From what I was told, the TOTAL <Dimension> is not Set Analysis. Look in the Reference Manual at the introduction to the aggregate functions. The syntax is in there on the tool tip when you are entering the expression. I don't remember if there is much of an explanation in the help.