Data Studio
5K members online now
5K 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.

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

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