Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you provide sample data for above pivot tables?
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
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
See Attached
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
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;