Pivot Table

[naziralala]

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

[MK_QSL]

Can you provide sample data for above pivot tables?

[naziralala]

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

[#4]

‌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

[vinieme12]

See Attached

[naziralala]

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

[vinieme12]

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;