topic Pivot Table in QlikView

Pivot Table

naziralala — Fri, 30 Dec 2016 11:39:31 GMT

Hello All,

I have a pivot table with 2 year dimensions.

Year1 Year2 2010 2011 2012

2010 55 - -

2011 - 23 -

2012 - - 45

The above value is Revenue, based on both years, for new Contracts.

I have another pivot table as below:

Year1 Year2 2010 2011 2012

2010 0 12 23

2011 - 21 24

2012 - - 34

The above value is Revenue, based on both years, for Upgrade Contracts.

I need a third pivot table as below:

Year1 Year2 2010 2011 2012

2010 0/55 12/55 23/55

2011 0/23 21/23 24/23

2012 0/45 0/45 34/45

Can anyone assist on how to do this with set analysis?

Thanks in advance.

Nazira

Re: Pivot Table

MK_QSL — Fri, 30 Dec 2016 11:50:14 GMT

Can you provide sample data for above pivot tables?

Re: Pivot Table

naziralala — Mon, 02 Jan 2017 17:15:55 GMT

Hey Manish,

I cannot provide any sample data as it is too big a file.

But, I am looking for set analysis expression which can divide the entire first row data of second table by the first row value in first table (like a constant). Also the contract type =new/upgrade and the years are 2 different, Year1 and Year2, which i use as dimensions.

Thanks,

Nazira

Re: Pivot Table

Mon, 02 Jan 2017 19:40:17 GMT

‌hi

your case can be seen as a case of 'multiple fact with different granularity'

a typical case is budget fact vs actual fact.

budget is one record per month, actual is one record per day.

and user wants to see the percentage of actual vs budget for the month every day.

i Remember Hendry has an article for the topic.

thanks

Re: Pivot Table

vinieme12 — Mon, 02 Jan 2017 20:35:14 GMT

See Attached

Re: Pivot Table

naziralala — Tue, 03 Jan 2017 03:03:31 GMT

Hey Vineeth,

I need it the other way round as in table 3, which is the output of the second table divided by the first table where Year1=Year2.

Please advise..

Thanks,

Nazira

Re: Pivot Table

vinieme12 — Tue, 03 Jan 2017 06:27:40 GMT

Try the below

TABLE1:

CrossTable(Year2,Y1Value,1)

LOAD Year,

// 'Table1' as TableIS,

[2010],

[2011],

[2012]

FROM

(ooxml, embedded labels, table is Sheet1);

temp2:

CrossTable(Year2,Y2Value,1)

LOAD Year,

// 'Table2' as TableIS ,

[2010],

[2011],

[2012]

FROM

(ooxml, embedded labels, table is Sheet2);

OUTER join (TABLE1)

Load * Resident temp2;

DROP TABLE temp2;

NoConcatenate

Table3:

LOAD

Year,

Year2,

NUM(sum(Y1Value/Y2Value),'##.#%') as Percentage

RESIDENT TABLE1

Group by Year,Year2;

Drop Table TABLE1;