Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Create a Pivot Table by association over multiple dimensions

I have to create a report to measure the Client Satisfaction Index (CSI) for each month region wise. which is calculated using the below formula:

1 - (complaints received for each month for each region / Inquiry received for each month in each region)

I get the data from these two table:

1) Complaint_table:

2) Inquiry_table:

Now I have associated these two tables on Year_month. I am stuck after this as I also want to associate over Region as otherwise how will I map the same two regions with each other.

Finally I want to create a pivot table with region as rows and columns as Year month and the measure as the CSI formula described above.

For e.g

Sep-18          Oct-18

Region       CSI                  CSI

A                 40%                 50%

B                 50%                 67%

C                  67%                0%

D                 67%                 67%

E                  33%                 50%

Thank You

Labels (4)

• ### MultidimensionChart

1 Solution

Accepted Solutions
Employee
Hi,

I've done this by loading all the data into one table and categorizing the data as either a complaint or an inquiry in a new field, Type.
Date([Complaint_Year Month],'MMM-YY') as YearMonth,
[Complaint_Region] as Region,
[Complaint_Name] as Name,
[Complaint_Service] as Service,
'Complaint' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join
Date([Inq_Year Month], 'MMM-YY') as YearMonth,
[Inq_Region] as Region,
[Inq_Name] as Name,
[Inq_Service] as Service,
'Inquiry' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet2);

Then in the pivot table use this expression to determine the CSI:
Count({<Type={Complaint}>}Name)/count({<Type={Inquiry}>}Name)
2 Replies
Employee
Hi,

I've done this by loading all the data into one table and categorizing the data as either a complaint or an inquiry in a new field, Type.
Date([Complaint_Year Month],'MMM-YY') as YearMonth,
[Complaint_Region] as Region,
[Complaint_Name] as Name,
[Complaint_Service] as Service,
'Complaint' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join
Date([Inq_Year Month], 'MMM-YY') as YearMonth,
[Inq_Region] as Region,
[Inq_Name] as Name,
[Inq_Service] as Service,
'Inquiry' AS [Type]
FROM [lib://Community/Example_data_source.xlsx]
(ooxml, embedded labels, table is Sheet2);

Then in the pivot table use this expression to determine the CSI:
Count({<Type={Complaint}>}Name)/count({<Type={Inquiry}>}Name)
Partner - Champion III

I agree with Lisa_P, that's what I would do too.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein