Unable to Compare Specific Values for NPS Between Two Dates in Looker Studio

[Esra4793]

Description:
I am working on a report in Looker Studio where I want to compare the NPS value for a specific date (e.g., March 20, 2024) with another specific date (e.g., February 21, 2024). However, I’m encountering the following issues:

1. When I create a filter for a single date, Looker Studio automatically calculates an average NPS value for the entire dataset instead of isolating the specific value for the chosen date.
2. I tried creating a calculated field using a CASE statement to extract the NPS value for a specific date, but Looker Studio doesn't recognize this field as a metric and instead treats it as a dimension.
3. Even when I aggregate the calculated field (e.g., using MAX or SUM), Looker Studio still does not allow me to properly compare the extracted NPS value for two dates.
4. Ultimately, I need to display the NPS value for each date in separate Scorecards and calculate the difference or percentage change between these two dates, but I cannot achieve this due to the issues above.
   
   Here some Screenshots: ( Iam working with Google Spreadsheet) 
   

[ArkadyZagdan]

@Esra4793 If your goal is to work only with monthly data pivot this view to have each nps value in column instead. This will allow you to use simple formulas using metrics.

example (Dec-Nov)/Dec

Optionally, in a current schema you can try use date comparison feature.
 

This way you can compare metrics on scorecards like examples here:
https://lookerstudio.google.com/u/0/reporting/fc021aef-6115-43d2-9ee4-07b47a7c1e1f/page/p_dmre7halad

But here you are bit limited with what you can achieve considering you only have data stamps for each month.

regards
Arkady Zagdan
Bigglo.pl

[Esra4793]

I tried this but it didnt work.
We are a company that provides professional training, and we track various KPIs such as dropout rates, NPS, and other performance metrics.

Issue 1: Incorrect Aggregation of Columns

Looker Studio is automatically aggregating values (e.g., AVG, SUM) directly from my Google Sheets data, treating all rows as a continuous dataset instead of treating each column separately. However, I need each column to be considered individually. I have already calculated the average in my dataset, so I don’t need Looker Studio to compute an average of the averages.

Issue 2: No Comparisons Over Time

Another major issue is that Looker Studio does not compare the values across different time periods. I have tried every possible data formatting option in Google Sheets, yet Looker Studio still does not generate meaningful comparisons between different dates.

I even structured my dataset to resemble the format used in other working reports (see attachment), but it still does not provide the expected comparisons.

Do you have any recommendations or best practices to resolve these issues? Any insights would be greatly appreciated!

[ArkadyZagdan]

It's really not clear to me what you seek to achieve.
I though M/M dynamics - I gave you a working solution.
If you rather would like to keep current schema and compute MoM dynamics for NPS maybe its easier to add separate column with dynamics and feed into the scorecard or table?

What do you mean by following? Can you put an example formula for your NPS so its easiet to understand what you are facing?

---

@Esra4793 wrote:

Looker Studio does not compare the values across different time periods.

---

y 

[Esra4793]

Exactly. For example, according to my spreadsheet, the NPS for the current month is 18.53, and for the previous month, it was 15.49. I want the scorecard in Looker Studio to show how much the value (18.53) has improved compared to the previous month.

However, I don’t know how to approach this because Looker Studio doesn’t provide these types of comparisons by default. Also, I want to keep the values in my table as they are—Looker Studio should not automatically calculate sums, averages, etc.

[ArkadyZagdan]

---

@Esra4793 wrote:

Exactly. For example, according to my spreadsheet, the NPS for the current month is 18.53, and for the previous month, it was 15.49. I want the scorecard in Looker Studio to show how much the value (18.53) has improved compared to the previous month.

---

I just reproduced your google sheets:

And followed the answer I gave you before with using dat range comparisons.
My scorecards shows this month to date and I am using coparison method to previous period.

And this is a results I am receving:

If this is not the goal, can you explain what is the goal you seek to achieve?

On a side note I am not seeing an issue with aggregating values. In your case there is no difference between avg/sum/max/min. No matter what you select it will show your value as you have one input per month.
Optionally you can add it as dimension and not metric.

Arkady

[Esra4793]

That's exactly what I want to achieve! This is exactly what I'm trying to do, but it's not working for me. Could it be because I'm using a comma instead of a dot for numerical values? Otherwise, my spreadsheet is set up exactly like yours.

Could you maybe show me the setup section (lookerstudio) in more detail?

And one more question:
When creating the scorecard, I didn’t use a table in Looker Studio beforehand, like you showed additionally. I just selected the scorecard directly as a chart. Should I maybe add a table as well, like you did?

I'm sorry, I'm still quite new to this field. 😊

[ArkadyZagdan]

No, I do not think it's about comma or dot as long as you see it as numeric it shoudl work fine.
Can you paste your setting of the scorecard? Especially the date range you are using and the comparison method?
I have added January data and this is the Looker Studio with my settings:
https://lookerstudio.google.com/u/0/reporting/4c5a5d0b-7c8c-47b9-b65b-e29a261509bf/page/ilkdE/edit

Hope it will help you get it solved!

Arkady

[Esra4793]

I did it exactly the same way as you did, with the same settings, but the comparison still doesn’t show up for me. 😕

Here are screenshots of my report and settings:


In the link you sent me, I also can't see the comparisons. 😕

[ArkadyZagdan]

Ah I see what you are trying to do.
You would like the table to dynamically filter the scorecard and it should dynamically show % vs moth preceding?

[Esra4793]

I have now selected the table additionally because you had it in your example as well.

However, I would be fine with just a single scorecard that shows a percentage comparison to the previous month.

All I really want is for the scorecard to display how the value has changed compared to the previous month—nothing more. No matter how, as long as it works!

[Esra4793]

I just want that like in the example:

[ArkadyZagdan · Accepted solution]

In order to reproduce you need to change from all dates to last month in scorecard settings.
My example assumes we already have January data but thats probably not true as the month not ended yet?

[Esra4793]

It finally worked! 😄 Thank you so much for your help.

I do have another question though: What if we want to make yearly or quarterly comparisons?

I tried applying the same principle for NPS, but Looker Studio added up the NPS values from the last three months instead of just showing the percentage change for the quarter.

I only want to compare how the value has changed over the quarter, not sum it up.

Sorry for all the questions, but you’ve been incredibly helpful! 😃

Attached is a screenshot of my attempt to compare the NPS score on a quarterly basis:

[ArkadyZagdan]

Happy that it finally worked for you.
Please mark this topic as resolved. Feel free to add new post with new question here and I will help you sort it.
Regards
Arkady

[Esra4793]

Thanks! I've marked this topic as resolved and opened a new one. I’d really appreciate it if you could help me there. Here is the link: 
https://www.googlecloudcommunity.com/gc/Looker-Studio/How-can-I-compare-values-on-a-yearly-or-quarte...