Aggregating on dimension where the values are technically not assigned

Wed, 01 Jun 2011 11:52:57 GMT

Hi Qlikperts!

It's me again with a question

I have the business requirement to aggregate values on a dimension for information purposes, where they are technically not allocated.

I tried to build up a simplified version of that problem:

Here is the technically correct distribution of the values and how they would appear in the pivot table:

AS IS
Year	Division	Product Group	Product	Amount
	A	Z	Prod1	100
	A	Y	Prod2	200
	A	X	Prod4	300
	B	Z	Prod1	100
	B	Z	Prod1	100
	A	-	-	100
	A	-	-	100

This is what the end-user wants to see:

TO BE
Year	Division	Product Group	Product	Amount
	A	Z	Prod1	400
	A	Y	Prod2	400
	A	X	Prod4	400
	B	Z	Prod1	800
	B	Z	Prod1	500

Please notice, that the values, assigned to the dimension "division", but with no assigned dimensions Product Group and Product have to be aggregated on the rows where Product Group and Product are assigned. The rows with no assigned ProductGroup and Product have to be 'eliminated'

I am aware that this would make the amounts redundant, but this is intended by the user. It doesn't make sense in this case, but it's just an example

Additionally, I would like to solve this problem in the pivot table expression, not in the script, although I know it would be better to solve in the script, performance wise.

Any help is appreciated! Thanks in advance.

topic Aggregating on dimension where the values are technically not assigned in QlikView

Aggregating on dimension where the values are technically not assigned