HII! This week I'm back with calculated field tutorial again! If you prefer reading, here's the blog post: danalyser.com/posts/data-studio-count-distinct-with-condition/ Come hangout at my Ko-Fi page too ! ko-fi.com/T6T25PD0A -SL
You’re very welcome Steven! I’m preparing some videos to be back to RUclips. Feel free to suggest new videos to me that you’re interested to see! Have a great day🤓
Thanks a lot Dana ♥. I fixed my problem. Instead of dividing # of sold items / # of transaction. I divided # of sold items / count_distinct (transaction_id)
Hey Danila, thanks so much for leaving this comment! Glad that you find it helpful :) If you have any video ideas, you're welcome to leave me a message here :) - SL
@@mohdzulfadhlimj9662 I see! I hope these videos are helpful for you. Feel free to share any content you would like to see from this channel so I could include in my planning 😊
Thank you for the video @danalyser. It helped me a lot. I am curious to know if you can use the same kind of formula from your tutorial but instead of using a specific text as the condition, use a lookup range as a condition. So for example count if email from table 2 features in table 1. Count(IF(customer_email (Table 2) = customer_email (Table 1), 1, Null))
Hey Jannik, thanks for the great feedback! I can only imagine this can be done when you left join table with the loookup table to be able to apply this formula :)
Hi Satrack! You're welcome! :) To answer your question, do you mind help me elaborate more what is ur problem ? maybe give a small sample of how your data looks like would be great.
Thank you for this video! I need little help with the formula " 1 - COUNT_DISTINCT(IF(Disability Label= "Undeclared",Personal Reference, NULL)) / COUNT(Disability Label) * 100 to get the percentage of disability declared. Percentage number of undeclared take away from 1.
hello, what if I want to combined 2 categories at the same time for the same chart Column Chart? I tried your methods, worked fine for a single category, but didn't work out for double category. Thanks!
I am trying to return the number of distinct states where total sales are greater than 500. The data is transactional, so it would require first calculating total sales (aggregated data) and then collecting the distinct count of states with the totalsales > 500 value. Since this logic violates the aggregate / non-aggregate rule, what's the best way I can achieve this?
I noticed you have several dates in your data set. Do you have issues with inaccurate reported numbers in Looker Studio when showing with this when comparing "Orders Placed" vs "Orders Shipped Out" during a certain time period? I am. Also, this problem translates to building a % formula for Orders Placed/Orders Shipped Out.
Hey Mike, glad you find it useful ! You can use IF() and AND operator, this documentation may be helpful to you: support.google.com/datastudio/answer/10468770?hl=en
@@danalyser Thank you so much for this reply. Although, I've tried the AND operator with my data it returned 0 even though there were customers who bought two products at one time. I know you'd probably be busy, but if you are available I would love to share my dataset with you to help me solve this problem. Either way, thank you so much for this video and the reply. Mike.
@@mikedechelsea hey Mike, sorry to hear that it’s not working for you. I do accept freelance work now which charged by hourly rate. If you’re interested, you may get in touch with me at hello@danalyser.com for more info :)
i have created a category from metric nut it is taking the category as metric i want it as a dimension, i tried cast function but it did not helped, can you help me?
Thank you for the excellent video. When I apply your method to my dataset I can calculate the number of "stationary sales," as a metric but when I try to use that number as divisor, with the numerator being another metric (say 'order amount' if that were in your dataset) Data Studio gives me an error about mixing metrics (aggregated values) and dimensions (non-aggregated values). Is there a workaround for this?
Thank you for watching Will! Yes, we can't mix unaggregated and aggregated fields in a calculation formula in GDS. In this case, for the divisor, you can use the formula SUM(order_amount). Hope it helps ! :) -SL
you could with the similar concept shared in this video, you may feel free to check out this SUMIF tutorial: ruclips.net/video/hkNVgPjjoHA/видео.html&lc=Ugxmu1_SrFy413J6opp4AaABAg
I'm sorry guys! I didn't know my audio is distorted all this while 😱😱 just discovered it today .. I'll use earphone for any editing from now onwards. 😢
I tried this to get the total number of sessions from source/medium = google/cpc and I got an error. How can you get a score card with a filter just of sessions from a certain channel?
Hi, I have a question. I have hundreds of products and I wanted to create a pie chart that groups products by how much they sold that week. So the pie chart would be the total sales, and there would be 4 groups: Products with $0 Sales, Products with $0-$100 in Sales, Products with $100-$200 in Sales, Products with More than $200 in Sales. I can't seem to find any solutions for this. I made a field that used Case data that grouped the Product Revenue data together into the above buckets, but I could only use it as a metric. I would like to make those groups dimensions, so I could see how many of my products fall within those groups.
I have a question, but firstly thank you for this tutorial - very useful as it's hard to find visual examples of these things for GDS My question/challenge is that I've created the count distinct for my topics (Product Category in your vid) and I want to divide that by a static number for each of the topics For example Topic 1 needs to be divided by 10 Topic 2 needs to be divided by 20 Topic 3 needs to be divided by 30 If my COUNT_DISTINCT returns 10 items for Topic 1, 15 items for Topic 2 and 20 items for Topic then then I would want to divide them (Topic 1 = 10/10, Topic 2 = 15/20 and Topic 3 = 20/30) How would I achieve that? Thank you
Hey Daniel, Thank you so much for your comments! I am glad that you appreciate the work I put into the video :) What I would do is to create an extra calculated field for divisor using CASE WHEN formula, then I would do the COUNT_DISTINCT(Topics) / AVG(divisor). I hope this helps or at least give you some idea on how to solve the problem. :) In case you need further private help, I have begin to provide technical consultation service for google data studio - you may reach out to my email (hello@danalyser.com) for more info. Happy Dashboarding! - SL
Hello! Nice video, very interesting! I would like to adapt it to what is my need, I would like to create a field that would count the unique events for a given event action, is your formula usable? Example, I have single event click-btn, I want a field that gives me the number of unique events it has triggered, how can I do? With the classic method of filters I know how to do it. My problem is that I have several events and I should insert them all in a table (so each column a event with unique events triggered). Thank you very much :)
Hey Davide, thank you for the feedback! I'm not sure whether this formula would fit what you need, may have to take a look on the dataset you have and understand further about your requirement (a table with several types of events count - if i understand your sentence correctly.) I'm currently offering technical consultation for GDS, we can discuss further via email if you wish. You may also contact me at theartclover@gmail [dot] com.
Hi SL, love your tips. Does Looker Studio allow you to do IF(OR( statements? Can you do IF(AND( statements like in excel and Big Query? E.g. COUNT_DISTINCT(if(AND(fiscal_year = 2024, Quartile =1), parent_client, null)) I can use the filter, but Means I have to make lots of filters for each Quartile. Side note, I don't have any date values in my data as year and Quartile = integer, financial periods = string.
Hi thank you very much for your video, it helps me a lot! I have a question about on how to count a duplicate data per row and give you the sample data in your email. It'd be my pleasure if you would give me some help with that. Hope you're healthy and happy always!
1 - (COUNT_DISTINCT(IF(Disability Label= "Undeclared", Personal Reference, NULL)) / COUNT(Personal Reference) * 100) the formula do not give desire output as it give -99 in all rows.
Hey Banji, any particular reason you put COUNT(Personal Reference) instead of COUNT_DISTINCT(Personal Reference) - I wasnt too sure what value you have in this field, could you share more so I could make some suggestions based on that? :)
Hi Danalyser, was wondering if you could assist me with an issue I havent been able to solve in data studio. I cant seem to find a way to message/email you. Am willing to pay for a solution
Hey Joseph, yes I’m willing to help. Do share me the problem you face and We’ll see if I’m able to provide some help to you. you may contact me at my instagram pm - @danalyser8
![Display TODAY's DATE as a TITLE in Data Studio [Calculated Field: CURRENT_DATE, MAX, CONCAT]](http://i.ytimg.com/vi/EVQ_nW19eq0/mqdefault.jpg)
![NLE Choppa - Or What ft. 41 (Orchestra Mix) [Official Music Video]](http://i.ytimg.com/vi/biOFASEgSOM/mqdefault.jpg)
![FULL Google Data Studio Course (2023) ➡️ rebranded to Looker Studio) [Compilation]](/img/1.gif)
HII! This week I'm back with calculated field tutorial again!
If you prefer reading, here's the blog post: danalyser.com/posts/data-studio-count-distinct-with-condition/
Come hangout at my Ko-Fi page too ! ko-fi.com/T6T25PD0A
-SL
In case you're looking for CASE WHEN tutorial: ruclips.net/video/ljmfdcmR_iY/видео.html
Enjoy
Your solution saved me a huge amount of time ! Thank you so much !!!!
You're SO welcome! Thanks for your support too! :)
Elegant solution for a quite painful problem.
Thanks a lot @Danalyser, you really deserve it.
Aww THANK YOU, you're so lovely! ❤️
Hi Danalyser- You teach me here how to fish. Respect as a teacher😃
Glad to know that!! Thanks for your lovely comments! 😊😊
Thank you for sharing the knowledge Danalyser 🙏🏾
Exactly what I was looking for!!! Thank you so much!
Glad that it helps! 🥰🥰
Very useful video! Been crunching this problem for 3 hours, thanks for saving me 🖤
This solution totally saved me. Thanks!
Great to hear!
Fantastic. Crisp explanation ❤
Glad you liked it! 😇
This was so helpful.
Thank you for the video
So glad you find it helpful Kesiena ! :)
You just saved my day! Great work and thank you for all that you do!
WoW THANK YOU!! 💛💛🙌🏻
Thanks! Very helpful.
Glad to know that it was helpful Andres! :)
Thank you Dana! Saved me a lot of time and energy from adding a new dataset T_T
Glad it helped! 😊
Thank you! this saves me some hours of reseach :)
Glad to hear this Jorge! :D
SL
Hi, thanks for the video!
You’re very welcome Steven! I’m preparing some videos to be back to RUclips. Feel free to suggest new videos to me that you’re interested to see! Have a great day🤓
Thanks a lot Dana ♥. I fixed my problem. Instead of dividing # of sold items / # of transaction. I divided # of sold items / count_distinct (transaction_id)
Great job! So happy that this video helps solve your problem! 🎉 Thanks for sharing, appreciate it! 💛
Seriously, thanks a lot :)
You're so welcome Mariel ! :) Feel free to share this with anyone that'd find this video useful ⭐️
Hi! Thank you for the video, this is exactly what I've been looking for!
Hey Danila, thanks so much for leaving this comment! Glad that you find it helpful :)
If you have any video ideas, you're welcome to leave me a message here :)
- SL
This video really help me to solve my current problem for my dashboard....thanks ya...
Hey! You're most welcome! Good to see you around :)
Just curious, are you also working as data professionals? :)
Not really... but my work deal with data...and still learning thru youtube...
@@mohdzulfadhlimj9662 I see! I hope these videos are helpful for you. Feel free to share any content you would like to see from this channel so I could include in my planning 😊
This is a great hack! I use it to calculate % by column in a table
YASSS I used to do that as well, that creates LOTS of dummy columns 😅 Sho excited to found this solution and share it with you all! 🙌🏻
Exactly what i am looking for, thanks🙂👌
Cool! You're so welcome Thapelo, glad that it was helpful to you :)
congrats for your channel
Thanks alot!! 🙏🏻
Thank you for the video @danalyser. It helped me a lot.
I am curious to know if you can use the same kind of formula from your tutorial but instead of using a specific text as the condition, use a lookup range as a condition.
So for example count if email from table 2 features in table 1.
Count(IF(customer_email (Table 2) = customer_email (Table 1), 1, Null))
Hey Jannik, thanks for the great feedback! I can only imagine this can be done when you left join table with the loookup table to be able to apply this formula :)
Excellent
Thank you ❤
Thank you for sharing
Thank you!
You’re welcome!
thanks! I like your vdo :)
You’re welcome! happy that you enjoy these videos 😁
Hi! Thank you for the video, i have other problem, i need to do a Sum Distinct,
you know how to do it?
Hi Satrack! You're welcome! :)
To answer your question, do you mind help me elaborate more what is ur problem ? maybe give a small sample of how your data looks like would be great.
Thank you for this video! I need little help with the formula " 1 - COUNT_DISTINCT(IF(Disability Label= "Undeclared",Personal Reference, NULL)) / COUNT(Disability Label) * 100 to get the percentage of disability declared. Percentage number of undeclared take away from 1.
hello, what if I want to combined 2 categories at the same time for the same chart Column Chart? I tried your methods, worked fine for a single category, but didn't work out for double category. Thanks!
I am trying to return the number of distinct states where total sales are greater than 500. The data is transactional, so it would require first calculating total sales (aggregated data) and then collecting the distinct count of states with the totalsales > 500 value. Since this logic violates the aggregate / non-aggregate rule, what's the best way I can achieve this?
HI THANKSSS!!
I noticed you have several dates in your data set. Do you have issues with inaccurate reported numbers in Looker Studio when showing with this when comparing "Orders Placed" vs "Orders Shipped Out" during a certain time period? I am. Also, this problem translates to building a % formula for Orders Placed/Orders Shipped Out.
Great video. My question is what if I want to count the number of stationary and another product say "Audio" and "Tech Gadget". How do I do it?
Hey Mike, glad you find it useful ! You can use IF() and AND operator, this documentation may be helpful to you: support.google.com/datastudio/answer/10468770?hl=en
@@danalyser Thank you so much for this reply. Although, I've tried the AND operator with my data it returned 0 even though there were customers who bought two products at one time. I know you'd probably be busy, but if you are available I would love to share my dataset with you to help me solve this problem.
Either way, thank you so much for this video and the reply.
Mike.
@@mikedechelsea hey Mike, sorry to hear that it’s not working for you.
I do accept freelance work now which charged by hourly rate. If you’re interested, you may get in touch with me at hello@danalyser.com for more info :)
i have created a category from metric nut it is taking the category as metric i want it as a dimension, i tried cast function but it did not helped, can you help me?
Thank you for the excellent video. When I apply your method to my dataset I can calculate the number of "stationary sales," as a metric but when I try to use that number as divisor, with the numerator being another metric (say 'order amount' if that were in your dataset) Data Studio gives me an error about mixing metrics (aggregated values) and dimensions (non-aggregated values). Is there a workaround for this?
Thank you for watching Will!
Yes, we can't mix unaggregated and aggregated fields in a calculation formula in GDS. In this case, for the divisor, you can use the formula SUM(order_amount).
Hope it helps ! :) -SL
can we do sum_distinct in Looker
you could with the similar concept shared in this video, you may feel free to check out this SUMIF tutorial: ruclips.net/video/hkNVgPjjoHA/видео.html&lc=Ugxmu1_SrFy413J6opp4AaABAg
I'm sorry guys! I didn't know my audio is distorted all this while 😱😱 just discovered it today .. I'll use earphone for any editing from now onwards. 😢
I tried this to get the total number of sessions from source/medium = google/cpc and I got an error. How can you get a score card with a filter just of sessions from a certain channel?
why is there no add field in my display?
Hi, I have a question. I have hundreds of products and I wanted to create a pie chart that groups products by how much they sold that week. So the pie chart would be the total sales, and there would be 4 groups: Products with $0 Sales, Products with $0-$100 in Sales, Products with $100-$200 in Sales, Products with More than $200 in Sales. I can't seem to find any solutions for this. I made a field that used Case data that grouped the Product Revenue data together into the above buckets, but I could only use it as a metric. I would like to make those groups dimensions, so I could see how many of my products fall within those groups.
Hi Leo 👋 we’ll need a bit more steps to do this, let me try to address this in a video ? Will comment here again when the video is up 😊
Hello, @@danalyser, how are you? Could you explain to me how I could solve this problem, please?
Hello, Leo, did you find any way to solve your problem? Could you help me? I'm in the same situation right now
@@gabriellourenco6136 hi thank you for asking! I’m doing good. The video is already up in my channel: ruclips.net/video/FB4R61L1FgM/видео.html
@@danalyser Thank you!!! You're awesome!
I have a question, but firstly thank you for this tutorial - very useful as it's hard to find visual examples of these things for GDS
My question/challenge is that I've created the count distinct for my topics (Product Category in your vid) and I want to divide that by a static number for each of the topics
For example
Topic 1 needs to be divided by 10
Topic 2 needs to be divided by 20
Topic 3 needs to be divided by 30
If my COUNT_DISTINCT returns 10 items for Topic 1, 15 items for Topic 2 and 20 items for Topic then then I would want to divide them (Topic 1 = 10/10, Topic 2 = 15/20 and Topic 3 = 20/30)
How would I achieve that?
Thank you
Hey Daniel, Thank you so much for your comments! I am glad that you appreciate the work I put into the video :)
What I would do is to create an extra calculated field for divisor using CASE WHEN formula, then I would do the COUNT_DISTINCT(Topics) / AVG(divisor).
I hope this helps or at least give you some idea on how to solve the problem. :)
In case you need further private help, I have begin to provide technical consultation service for google data studio - you may reach out to my email (hello@danalyser.com) for more info.
Happy Dashboarding!
- SL
Hello! Nice video, very interesting! I would like to adapt it to what is my need, I would like to create a field that would count the unique events for a given event action, is your formula usable?
Example, I have single event click-btn, I want a field that gives me the number of unique events it has triggered, how can I do? With the classic method of filters I know how to do it. My problem is that I have several events and I should insert them all in a table (so each column a event with unique events triggered).
Thank you very much :)
Hey Davide, thank you for the feedback! I'm not sure whether this formula would fit what you need, may have to take a look on the dataset you have and understand further about your requirement (a table with several types of events count - if i understand your sentence correctly.)
I'm currently offering technical consultation for GDS, we can discuss further via email if you wish. You may also contact me at theartclover@gmail [dot] com.
Hi SL, love your tips. Does Looker Studio allow you to do IF(OR( statements? Can you do IF(AND( statements like in excel and Big Query?
E.g. COUNT_DISTINCT(if(AND(fiscal_year = 2024, Quartile =1), parent_client, null))
I can use the filter, but Means I have to make lots of filters for each Quartile. Side note, I don't have any date values in my data as year and Quartile = integer, financial periods = string.
Yes, you can use Case When, For example SUM(CASE WHEN Reporting Year = 2022 THEN Cost ELSE 0 END)
Hi thank you very much for your video, it helps me a lot!
I have a question about on how to count a duplicate data per row and give you the sample data in your email. It'd be my pleasure if you would give me some help with that.
Hope you're healthy and happy always!
Thanks again Atikah, nice talking to you earlier and hope to see you around more ! Happy learning 😊
@@danalyser Thanks for the help! It’s also great talking with you😊😊
1 - (COUNT_DISTINCT(IF(Disability Label= "Undeclared", Personal Reference, NULL)) / COUNT(Personal Reference) * 100) the formula do not give desire output as it give -99 in all rows.
Hey Banji, any particular reason you put COUNT(Personal Reference) instead of COUNT_DISTINCT(Personal Reference) - I wasnt too sure what value you have in this field, could you share more so I could make some suggestions based on that? :)
Hi Danalyser, was wondering if you could assist me with an issue I havent been able to solve in data studio. I cant seem to find a way to message/email you. Am willing to pay for a solution
Hey Joseph, yes I’m willing to help. Do share me the problem you face and We’ll see if I’m able to provide some help to you. you may contact me at my instagram pm - @danalyser8
This is great! Thank you
You are very welcome!! 💛