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

Balance equal or more than 0

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi! I have list of "balance" which consists of both negative and positive numbers in my google sheets which was linked with Data Studio.

I was wondering if it's possible to create formula (In Google Data Studio), which filters those - only positive or 0 (will then call it Income); negative (will then call it Expense)

Something like "Balance => 0"

 

 

So far nothing works Smiley Sad

Thank you

 

Balance equal or more than 0

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

 

If I'm understanding this correctly, you should be able to create your own dimension that calculates whether the balance is positive or negative.

 

Calculated Fields

https://support.google.com/datastudio/answer/6299685?hl=en

 

Function List

https://support.google.com/datastudio/table/6379764

 

A simple case statement like so:

 

CASE

WHEN balance >= 0 THEN "Positive"

WHEN balance <= 0 THEN "Negative"

END

 

You should then be able to add this new dimension as a filter to the report.

Balance equal or more than 0

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks, i already tried this, but something seems to be wrong:

https://d2ppvlu71ri8gs.cloudfront.net/items/362n1o1c2o2d1I040w2W/Image%202017-05-02%20at%2010.56.04%...

 

 

Balance equal or more than 0

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

i tried to do it and i can't because your "balance" is a number field, then your result field should be numeric too.

But you want that the result value will be a text with "positive" or "negative". 

 

It's why i think it will not working. 

 

I tried this : 

1 - CASE WHEN Balance>=0 THEN "Positive" ELSE "Negative" END : Result is "Nan" and the field type is text. 

If i change the field type to number, my result is "null"

2 - CASE WHEN Balance>=0 THEN 1 ELSE 0 END : Result is working, it gives me 0 or 1

 

If you work with a csv to import in Data Studio, it's better to made a field "Positive/negative" and to set the value.

Re: Balance equal or more than 0

Explorer ✭ ✭ ☆
# 5
Explorer ✭ ✭ ☆

@Klāvs B

 

Well, i worked a lil about your problem and i found a solution. You will have to create 2 fields Smiley Happy

Be sure to copy my formula as i do, don't change or add quote ( i put your name field).

 

  1. Create a new field "Balance2" with this formula : 
    CASE WHEN Balance >= 0 THEN '1' ELSE '0' END
    Your field will be created in type "text" and aggregation "auto"

  2. Create an other field "Positive_negative" with this formula : 
    CASE WHEN Balance2 = '1' THEN 'Positive' ELSE 'Negative' END
    Your field will be created in type "text" and aggregation "None"
  3. It's done. You can now click "finish" and to add your new field in your chart. 

positive_negative.PNG

I hope it will help you Smiley Happy