topic Re: Some help on creating a table... in QlikView

Some help on creating a table...

Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I don't exactly know how this kind of table is called in English, but I have dug through some posts and looked for similar images like mine but did not find a solution. It must be simple, but as always, you need to know where to click...

I am trying to create this table (from XLS) in QV:

Sometimes they are referred to as a 'distance' or 'mileage' tables but mine is a little different.

The list of cities on the left is different than the one on top. So I have two dimensions and need a count that will give the number of sales a customer from (eg.) Atlanta buys in Omaha.

I would be very thankfull if someone could throw in a few hints...

Alexander

Re: Some help on creating a table...

oknotsen — Sat, 05 Mar 2016 15:53:37 GMT

That is called a Pivot Table.

What you do is create a chart and on the General tab select the chart type at the end of the first row.

Be sure to add both dimensions and your expression.

When finished, unfold the dimensions, click on the second dimension, keep your mouse button pressed and start dragging it around. It might take making some circles with your mouse but at some point the arrow will flip direction. Let go of your mouse and the dimension now shows up at the top.

Turn on "partial sums" on the Presentation tab to add that total column.

Good luck!

Re: Some help on creating a table...

sunny_talwar — Sat, 05 Mar 2016 15:54:12 GMT

Seems from the image that something similar can be accomplished using a pivot table. Have you tried using a pivot table?

Re: Some help on creating a table...

Sat, 05 Mar 2016 16:00:25 GMT

Hi Onno,

Yes I have been playing around with pivot tables (both options from the Chart menu). Also got the formula in there, but they are always presented next to each other... I just don't get one horizontal and one vertical. So I guessed I should leave the pivot environment all together...

If I 'grab' a column header I can only move them left and right...

Alex

Re: Some help on creating a table...

Sat, 05 Mar 2016 16:01:36 GMT

Thanks Sunny,

You are again very quickly to help someone. Thank you so much...

I will start all over with the pivot tables and see if I can get it to work.

Alex

Re: Some help on creating a table...

martinpohl — Sat, 05 Mar 2016 16:08:58 GMT

Hello,

it looks like a pivot table.

Do a right click with your mouse, new object/chart

Select pivot table (upper row, the object on the right)

Add dimension Client city and Location city / Next

Create an expression count (distinct SalesId)

Finish the object.

Do a right click on City location / Expand all

Click on Location city, keep click and move Location city above your expression.

If you want a total by your Cities go to presentation and select subtotals on both dimensions

Regards

Re: Some help on creating a table...

Sat, 05 Mar 2016 16:11:42 GMT

Think I am getting there... Slowly. But Yes Onno, I managed to get something on top.

Will post the final result. Thanks for that tip. It makes all the difference!

(Unfortunately all my messages seems to need to pass a moderator so I cannot respond in 'real' time. I don't know when I will be 'accepted').

Thank you all for your time to help me! It is highly appreciated.

Alex

Re: Some help on creating a table...

oknotsen — Sat, 05 Mar 2016 16:16:20 GMT

It feels that way, but if you make circles with your mouse near the top-right corner, you will see the arrow turn directions. I know, it can take some shacking and it is annoying the first time till you get it right, but if it is indeed a Pivot Table it will happen eventually .

Re: Some help on creating a table...

oknotsen — Sat, 05 Mar 2016 16:26:01 GMT

Alexander Bosch wrote:

(Unfortunately all my messages seems to need to pass a moderator so I cannot respond in 'real' time. I don't know when I will be 'accepted').

Hi,

That will stop after you scored a few hundred points. No, I do not know the exact threshold.

I do happen to be moderator, so I have been the one "approving" your replies. I know the pain of having to wait for a moderator to show up and I can tell you we are not really active during the weekend as most of us do this in their spare time (my real job being Senior Technical Trainer for Qlik).

... and since I am now going off-line for the rest of the evening (I am based in the Netherlands), don't be disappointed it takes a few hours or over half a day before your next post gets approved. Just know it is not personal; it is to keep the spam bots out.

Re: Some help on creating a table...

Sat, 05 Mar 2016 16:29:39 GMT

Duidelijk verhaal Onno... Ik snap het. Dank voor deze aanvullende toelichting en bedankt voor je inzet voor de community! Fijn weekend!

Alexander

(For English natives: I just thanked Onno in our language for the additional support about the moderation).

Re: Some help on creating a table...

Digvijay_Singh — Sat, 05 Mar 2016 16:32:14 GMT

This sample may help -