Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Ignore Dimension in Pivot Table

Hi guys,

i am struggling with following issue:

let's say i have raw data like:

YearCitySpent
2014frankfurt1200
2014munich3000

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

&

CitycustomerId
frankfurt1
frankfurt2
munich5
munich6
munich7

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
customerIdSpent per customerId
2014frankfurt1600
2014frankfurt2600
2014munich51000
2014munich61000
2014munich71000

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

Hope somebody can help

1 Solution

Accepted Solutions
Partner
Partner

Re: Ignore Dimension in Pivot Table

ok

so use this expression

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

10 Replies
Partner
Partner

Re: Ignore Dimension in Pivot Table

hi you can try

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

qlikmsg4u
Valued Contributor

Re: Ignore Dimension in Pivot Table

Like this?

Re: Ignore Dimension in Pivot Table

Try this expression:

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


Capture.PNG

Not applicable

Re: Ignore Dimension in Pivot Table

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

Partner
Partner

Re: Ignore Dimension in Pivot Table

ok

so use this expression

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

Partner
Partner

Re: Ignore Dimension in Pivot Table

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

try this

=if(Dimensionality()<=2,

sum(Spent),

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

Untitled1.jpg

Untitled2.jpg

Please see the attached

Re: Ignore Dimension in Pivot Table

Try this in the pivot table:

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


Capture.PNG

sasiparupudi1
Honored Contributor III

Re: Ignore Dimension in Pivot Table

Like this?