topic Re: Create a Pivot Table by association over multiple dimensions in App Development

Create a Pivot Table by association over multiple dimensions

kushagra_jain — Sat, 16 Nov 2024 20:53:05 GMT

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%

I am not sure how to do this analysis, Please help me out.

Thank You

Re: Create a Pivot Table by association over multiple dimensions

Lisa_P — Wed, 15 May 2019 01:09:05 GMT

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.
LOAD
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
LOAD
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)

Re: Create a Pivot Table by association over multiple dimensions

jonathandienst — Wed, 15 May 2019 06:17:14 GMT

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