AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
1.9K members online now
1.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

How to calculate empty fields?

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

In Google Data Studio I would like to make a dashboard that gives a clear overview on how good e specific field is filled with data.
For example I have 3000 records in this specific field and more than 1000 cells have no data in it.

I would like to make a table or pie-chart that shows me how good the fill rate is for this field.

 

I'm trying to get results with this formula:

CASE WHEN KorteBeschrijvingOpleiding = 'null' THEN 'filled' ELSE 'empty' END

 

The problem is that 'null' doesn't work and also other things I try to put here doesn't give back the correct ammount of empty cells.

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author GJ B
August 2017

How to calculate empty fields?

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi GJB

The correct formula for your CASE is:
CASE WHEN KorteBeschrijvingOpleiding IS NULL THEN 'filled' ELSE 'empty' END

 

 

View solution in original post

How to calculate empty fields?

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

Where is your data coming from? If it's from Sheets, it might be easier to add a dimension Y/N specifying whether the field is filled, then you can create a bar/table/pie that compares the Y/N condition.

How to calculate empty fields?

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks for your reply Litmon.

The data is coming from a odata api feed which i convert into a Google Spreadsheet.

The spreadsheet i will than upload to Google Data Studio.

 

The problem with maken an additional field in the spreadsheet is, that I than should make this Y/N condition for every field I want to make the calculation and I also have to redo this for every new spreadsheet I make.

Marked as Best Answer.
Solution
Accepted by topic author GJ B
August 2017

How to calculate empty fields?

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi GJB

The correct formula for your CASE is:
CASE WHEN KorteBeschrijvingOpleiding IS NULL THEN 'filled' ELSE 'empty' END

 

 

How to calculate empty fields?

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Thanks for the reply Arturo.

It works perfectly