topic Re: Use of column-value in pivottabel in QlikView

Use of column-value in pivottabel

intervigilium — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Use of column-value in pivottabel

intervigilium — Fri, 07 Oct 2016 07:52:11 GMT

Anyone any idea? Still struggling with this

Re: Use of column-value in pivottabel

sunny_talwar — Fri, 07 Oct 2016 16:12:09 GMT

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 )

Re: Use of column-value in pivottabel

intervigilium — Tue, 11 Oct 2016 10:59:23 GMT

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!

Re: Use of column-value in pivottabel

intervigilium — Tue, 11 Oct 2016 16:05:15 GMT

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...

Re: Use of column-value in pivottabel

sunny_talwar — Tue, 11 Oct 2016 16:22:12 GMT

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

Re: Use of column-value in pivottabel

intervigilium — Tue, 11 Oct 2016 18:20:27 GMT

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!

Re: Use of column-value in pivottabel

sunny_talwar — Tue, 11 Oct 2016 18:26:39 GMT

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

Best,

Sunny

Re: Use of column-value in pivottabel

intervigilium — Wed, 19 Oct 2016 19:13:15 GMT

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?

Re: Use of column-value in pivottabel

sunny_talwar — Wed, 19 Oct 2016 20:08:45 GMT

Here try this:

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

Re: Use of column-value in pivottabel

intervigilium — Wed, 19 Oct 2016 20:13:41 GMT

Unbelievable......i spend hours on trying....you have the solution in a few minutes!

I'm jealous at your qlikview-knowledge!

Thanks again!

Re: Use of column-value in pivottabel

sunny_talwar — Wed, 19 Oct 2016 21:03:15 GMT

No problem at all, I am glad I was able to help and make you feel a little jealous