Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
kushagra_jain
Contributor II
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: 

Complaint_data.PNG

2) Inquiry_table:

Inquiry_data.PNG

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

1 Solution

Accepted Solutions
Lisa_P
Employee
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.
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)

View solution in original post

2 Replies
Lisa_P
Employee
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.
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)

View solution in original post

jonathandienst
Partner
Partner

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