Table Transformer - Calculation NPS out of Pivot data

[Escmarc]

Hi community

I have a table and would like to calculate the NPS (Net Promoter Score) per quarter with the "table transformer" plugin. Data input is dynamic and data are grouped automatically from a CSV. Can someone help me to create a working sql statement? 

Input: Table transformer table

Table transformer Formula from a working example

SELECT
/*Calc NPS
https://omf.ai/lexikon/nps/
*/
ROUND(
((COUNT(CASE WHEN 'Literal of How likely are you to recommend the TDE App Party to a colleague?' >= 9 THEN 1 END)
/COUNT(*))*100)-

((COUNT(CASE WHEN 'Literal of How likely are you to recommend the TDE App Party to a colleague?' <= 6 THEN 1 END)
/COUNT(*))*100),2)

AS NPS
FROM T1

Expected Output

Fertigstellungszeit : NPS

2023 Q1 : 0

2023 Q3 : -25 

Data input from CSV

[Stiltsoft support · Accepted solution]

Hi @Escmarc ,

We can suggest trying the following workaround:

Here my manually created table is a replication of your pivot table.

Then in the internal Table Transformer macro we flatten the array:

SEARCH / AS @a EX(TEXT('Col 2')->split(";")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

In the second Table Transformer macro we calculate the types of the answers:

SELECT
'Col 1',
SUM(IF('Type' = "Promoter", 1, 0)) AS 'Promoter',
SUM(IF('Type' = "Detractor", 1, 0)) AS 'Detractor',
SUM(IF('Type' = "Medium number", 1, 0)) AS 'Medium number'
FROM
(SELECT *,
CASE
WHEN 'Col 2' >= 9 THEN "Promoter"
WHEN 'Col 2' <= 6 THEN "Detractor"
ELSE "Medium number"
END
AS 'Type'
FROM T*)
GROUP BY 'Col 1'

And in the external Table Transformer macro we calculate the NPS:

SELECT 'Col 1',
'Promoter'/('Promoter'+'Detractor'+'Medium number')*100 -
'Detractor'/('Promoter'+'Detractor'+'Medium number')*100 AS 'NPS'
FROM T*

Hope it helps your case.

[Escmarc]

@Stiltsoft support ,

Ingenious approach. I didn't realise that this kind of nesting worked. I tested and it worked. Thank you very much!