Selective Totals for data with different Metrics

Sat, 25 Jun 2011 02:39:52 GMT

Hi,

I was wondering if its possible to display a selective total for a Pivot table. My challenge is that I have to display a Pivot table with data that has 2 different metrics of measurement.

For instance in my source data I have 2 tables like this:

GOALS:

COUNTRY	GOAL VALUE
United Kingdom	100 Units
Spain	200 Units
Germany	300 Units
USA	$6000 Dollars

SALES:

SALE COUNTRY	SALE AMOUNT
United Kingdom	25 Units
United Kingdom	50 Units
Spain	100 Units
Spain	100 Units
Germany	150 Units
USA	$3000
USA	$3000

The organization in question defines its U.S. sales goals in terms of dollars and rest of the world sales goals in terms of units.

Desired Result in QlikView:

Note that in the TOTAL row below I only want to display the UNITS value in the Total and NOT include the Dollar amount.

Country	Goal	Actual Sales	Percentage of Goal
United Kingdom	100	75	75%
Spain	200	200	100%
Germany	300	150	50%
USA	6000	6000	100%
TOTAL UNITS	600	425	70.83%

Is this possible in QlikView? I have not found a way to exclude a certain Expression value from the Total calculation.

A second challenge is displaying the Units, ideally I'd like to be able to display the US sales as $6000 and the other Sales as 75 Units, 200 Units and 150 Units. Not sure if this is possible either.

I hope the above example helps explain the issue. Any help or advice would be greatly appreciated!

Thanks!

Anosh

Message was edited by: awadia Forgot to mention, I am using QlikView v10 for this document.

topic Selective Totals for data with different Metrics in QlikView

Selective Totals for data with different Metrics