Use of column-value in pivottabel

[intervigilium]

Hi all,

I have created a pivottable, where the columns represents customers within a selection (dynamic).

In every colum i want a expression (sum of values with certain criteria) for that particular customer.

In current version it calculates the sum over all customers because the column (clientnr) is not a selection.

Any ideas ? Am a reel beginner and searched a long time on the forum, but didnt find a simular situation.

I attached a screenshot. The red box represent the total value, but i want that value related to the customer in de column.

Current formule in the variable is:

= Sum( { $<strGrootboekGroupCode= {"HC.1.*" }>}lngGrootboekSaldo)

I'm looking for something like:

= Sum( { $<strGrootboekGroupCode= {"HC.1.*" }>,<Clientnr_=Columnheader>}lngGrootboekSaldo)

Ofcourse the second is not working, only to clearify what i want to achieve

Any help?

[intervigilium]

Anyone any idea? Still struggling with this

[sunny_talwar]

Would you be able to share a sample from your application to see the issue and play around with it? Also specify an expected output from the sample you will provide (if you will )

[intervigilium]

Hi Sunny,

Thanks for your reply. Due to the fact i have customer-related data in the file i will make a  "prototype" with the same problem and post it here.

Coming soon!

[intervigilium]

I created an simple model in Qlikview with the same issue

I have a simple pivot table with the sum of balances on ledgernumers for each customer.

The sum is auto-sum in Qlikview.

What i want:

For each customer 1 extra row with the sum of balances where range x (where x is "4" or higher)

It should give (in excel)

                                            Customer 1      Customer 2      Customer 3

sum of range 4 or higher          300                    2100                    950

100                                        250

200                                        2000

300                                        300

400

and more...

[sunny_talwar · Accepted solution]

Do you want the total and the range 4 of higher total?

[intervigilium]

wow...that is exactly what i needed.....

Need some time to understand how you fixed that, but will dive into it

Thanks for your time and solution!

[sunny_talwar]

No problem at all, I am more than happy to explain if you don't understand something.

Best,

Sunny

[intervigilium]

Hi Sunny,

I worked quite further on the model. Which is great thanks to your solution.

Only i have a another question. I doubt if i should start a new topic....

Using your solution, i want to add a column for each customer which returns the percentage of the balance in relation to the sum of balance which match the selection =>4 (description_num)

I added "my solution" and als  an excel to explain more what i am trying to achieve. Can you help again?

[sunny_talwar]

Here try this:

=Balance / Sum(TOTAL <[Customer ID]> {<Description_Num = {'>=4'}>}Balance)