topic Re: Straight/Pivota Table in QlikView

Straight/Pivota Table

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

Hi ,

I want to create the bleow table with the same formate which is mention below, Can any one please help me how we can create the below table

Regards,

Nisha

Re: Straight/Pivota Table

adamdavi3s — Tue, 28 Mar 2017 11:24:36 GMT

Hi Nisha,

I think you need to add another dimension which groups whatever dimension it is you're already using.

This will be the most straight forward way.

You could potentially do it with a valuelist dimension and pick match but that's more involved.

More than happy to advise further but a sample qvw would really be required to advise more accurately.

Re: Straight/Pivota Table

nisha_rai — Tue, 28 Mar 2017 11:41:09 GMT

Thanks Adam,

I don't have any sample qvw, I have only that image and the rquirement.

Requirement is :

Cost Type: Plan, Actual, Forecast (Will always be two of these plus a variance)

Cost: Monthly, Year to Date, Full Year (Will only every be one of these)

Account Type: This will be at one of the 4 levels of the accounting mapping hierarchy (Likely to be levels 2, 3, or 4 only)

Grouping: This could be one of two groupings a) Site (HK, Ldn, NY, Paris, RoW) or Country, or b) Product or sub-product (They would want some of the metrics to show Site or Product side by side, i.e. compare HK, Ldn, NY, Paris, RoW.

Metrics: Current view and trending

Based on that we have to create the above table. Which are in White Color row are the Different Dimension and Which are in Gray Color are different dimension. Both Dimensions may be increase in future

Re: Straight/Pivota Table

adamdavi3s — Tue, 28 Mar 2017 11:45:56 GMT

Hi Nisha,

Sorry I can't help much here without something to work with, but I am guessing your rows are showing account types?

If so then just add a dimension which groups these into the required summary levels, then use a pivot table to generate the required totals.

Re: Straight/Pivota Table

vinieme12 — Tue, 28 Mar 2017 12:07:44 GMT

please post sample app/ data

Preparing examples for Upload - Reduction and Data Scrambling

Re: Straight/Pivota Table

nisha_rai — Tue, 28 Mar 2017 12:31:16 GMT

Hi ,

Please have a look below are the sample data tables

based on that we have to prepare above table. 1 to 5 row comes from Account Name and 6th row is the L1 Name etc.

Re: Straight/Pivota Table

adamdavi3s — Tue, 28 Mar 2017 12:33:10 GMT

Ok so it looks like you have all the data you need to create a pivot table here?

Re: Straight/Pivota Table

adamdavi3s — Tue, 28 Mar 2017 12:34:59 GMT

Oh if you can just copy and paste the tables from excel into a reply, then I can create an example for you.

I.e. paste the actual data, the community should then translate it into a table which I can download- not typing out those tables from an image 😉

Re: Straight/Pivota Table

nisha_rai — Tue, 28 Mar 2017 12:36:10 GMT

How Creation of group works here?

Re: Straight/Pivota Table

nisha_rai — Tue, 28 Mar 2017 12:38:37 GMT

Hi,

Acount Name	L4 Name	L3 Name	L2 Name	L1 Name
BI	Market	Market	IT	Toatl& IT
IT	Market	Market	IT	IT
Water	Market	Market	Total	Total & IT
Gas	Market	Market	Total	Total & IT
Electricity	Host	Host	Host	Host
WiFi	Host & network	Host & network	Host	Host
Broadband	Host & network	Host & network	Host	Host
Finance	Bonus	Bonus	Indirect	Indirect
HR	HR	HR	Indirect	Indirect
Support	Support	Support	Indirect	Indirect

Account Name	Monthly Jan Cost	YTD Jan Cost
BI	100	-175
IT	-50	100
Water	20	-80
Gas	-25	75
Electricity	150	-35
WiFi	125	-10
Broadband	-35	50
Finance	50	100
HR	-20	80
Support	100	-25

Account Name	Full Year Cost
BI	200
IT	100
Water	50
Gas	-20
Electricity	10
WiFi	200
Broadband	-75
Finance	50
HR	125
Support	100

Re: Straight/Pivota Table

adamdavi3s — Tue, 28 Mar 2017 12:45:36 GMT

Ok now we have a starting point

Please see the attached example and lets work out what needs to change to meet your requirement

Re: Straight/Pivota Table

nisha_rai — Tue, 28 Mar 2017 12:50:49 GMT

Hi ,

In place of total I want the L1 Name, I don't want the L1 Name as a seperate column

Re: Straight/Pivota Table

vinieme12 — Wed, 29 Mar 2017 07:06:12 GMT

See Attached sample app

Re: Straight/Pivota Table

nisha_rai — Wed, 29 Mar 2017 08:37:00 GMT

Thanks Vineeth,

If our Account Name increase then ,is that solution is feasible. And can we make that dimesion dynamic

Re: Straight/Pivota Table

vinieme12 — Wed, 29 Mar 2017 12:01:07 GMT

You will have to manage the inline table

But i'll try to think of any other ways

Re: Straight/Pivota Table

vinieme12 — Tue, 04 Apr 2017 11:20:13 GMT

See this post

Partial Sums in straight table | Custom Labels in Pivot SubTotals

Re: Straight/Pivota Table

nisha_rai — Tue, 04 Apr 2017 12:52:48 GMT

Thanks Vineeth, But that is not exactly what I want, I don't want Extra Column, For example

As per your attachment you have two column one is Display Country List and other is Name, i want the display country name comes below the Name row

Like that

Name	2016	2017
Ted	40000	48000
Sienfield	60000	72000
US	100000	120000
Jack Sparrow	50000	60000
Sherlock Homes	35000	42000
UK	85000	102000
Takeshi	30000	36000
Naruto	80000	96000
Japan	110000	132000
All Region Cost	295000	354000