topic Re: Ignore Dimension in Pivot Table in QlikView

Ignore Dimension in Pivot Table

Wed, 02 Sep 2015 08:25:06 GMT

Hi guys,

i am struggling with following issue:

let's say i have raw data like:

Year	City	Spent
2014	frankfurt	1200
2014	munich	3000

with this data i know how much money was spent for each city

City	customerId
frankfurt	1
frankfurt	2
munich	5
munich	6
munich	7

with this data i know which customer is connected to which city.

What i want to achieve know in Pivot Table is :

Based on the count of customers grouped by Year and City i want to Calculate the Column "Spent"

My Problem is i need to ignore the dimension customerId for the calculation of "Spent". I only know how mauch was spent in each city but not how much was spent per customerId. So i want to count the CustomerId per city and then Spent devided through Count of CustomerIds per city

so let's say: munich spent 3000 and has 3 connected customerIds --> 3000/3 = 1000

That should be the result:

Year	City	customerId	Spent per customerId
2014	frankfurt	1	600
2014	frankfurt	2	600
2014	munich	5	1000
2014	munich	6	1000
2014	munich	7	1000

i tried a lot with aggr and rangesum functions but didn't get it work yet.

Hope somebody can help

Re: Ignore Dimension in Pivot Table

lironbaram — Wed, 02 Sep 2015 08:34:29 GMT

hi you can try

sum(total<Year,City>Spent)/count(total<Year,City>customerid)

Re: Ignore Dimension in Pivot Table

qlikmsg4u — Wed, 02 Sep 2015 08:37:55 GMT

Like this?

Re: Ignore Dimension in Pivot Table

sunny_talwar — Wed, 02 Sep 2015 08:39:00 GMT

Try this expression:

=Sum(Spent)/Count(TOTAL <City> customerId)

Re: Ignore Dimension in Pivot Table

Wed, 02 Sep 2015 08:55:52 GMT

thank you all for your fast answers.

your solutions are all in Straight Table.

But i need to show this in Pivot Table.

The problem with pivot Table is that i get wrong Totals!

Like when the City is not expanded the result should be 3000 for munich and when its expanded to customerId the result should be 1000 for each customer

Re: Ignore Dimension in Pivot Table

lironbaram — Wed, 02 Sep 2015 09:19:57 GMT

so use this expression

sum(aggr(sum(Spent)/count(Total<Year,City> customerId),customerId,City,Year))

Re: Ignore Dimension in Pivot Table

awhitfield — Wed, 02 Sep 2015 09:25:50 GMT

Hi Steve,

try this in a pivot please:

If (Dimensionality() = 1,Sum(Spent), If (Dimensionality()= 2, Sum(Spent),sum(total<Year,City>Spent)/count(total<Year,City>CustomerId)))

HTH - Andy

Re: Ignore Dimension in Pivot Table

Kushal_Chawda — Wed, 02 Sep 2015 09:32:52 GMT

try this

=if(Dimensionality()<=2,

sum(Spent),

sum(total<CustomerID>Spent)/count(TOTAL<City>CustomerID))

Please see the attached

Re: Ignore Dimension in Pivot Table

sunny_talwar — Wed, 02 Sep 2015 09:48:58 GMT

Try this in the pivot table:

=Sum(Aggr(Sum(Spent)/Count(TOTAL <City> customerId), Year, City, customerId))

Re: Ignore Dimension in Pivot Table

sasiparupudi1 — Wed, 02 Sep 2015 10:19:41 GMT

Like this?

Re: Ignore Dimension in Pivot Table

Thu, 03 Sep 2015 07:10:25 GMT

thank you all for yor fast replies