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
@danalyser
2 жыл бұрын
In case you're looking for CASE WHEN tutorial: kzitem.info/news/bejne/zaCjzJiZpoSQnI4 Enjoy
@startplo12
Жыл бұрын
Very useful video! Been crunching this problem for 3 hours, thanks for saving me 🖤
@fahmifajriansyah1
11 ай бұрын
Thank you for sharing the knowledge Danalyser 🙏🏾
@yoray_s
Жыл бұрын
Elegant solution for a quite painful problem. Thanks a lot @Danalyser, you really deserve it.
@danalyser
Жыл бұрын
Aww THANK YOU, you're so lovely! ❤️
@mustang6820
Жыл бұрын
Exactly what I was looking for!!! Thank you so much!
@danalyser
Жыл бұрын
Glad that it helps! 🥰🥰
@user-xm6il5kh4k
5 ай бұрын
Your solution saved me a huge amount of time ! Thank you so much !!!!
@danalyser
5 ай бұрын
You're SO welcome! Thanks for your support too! :)
@danilanefedov1172
2 жыл бұрын
Hi! Thank you for the video, this is exactly what I've been looking for!
@danalyser
2 жыл бұрын
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
@user-np6rs8hg5b
Жыл бұрын
You just saved my day! Great work and thank you for all that you do!
@danalyser
Жыл бұрын
WoW THANK YOU!! 💛💛🙌🏻
@kenjisano4125
4 ай бұрын
This is great! Thank you
@danalyser
3 ай бұрын
You are very welcome!! 💛
@jayanimahatantila68
Жыл бұрын
Thank you ❤
@user-or4zq9kx3w
10 ай бұрын
Thank you for sharing
@andreslugo935
Жыл бұрын
Thanks! Very helpful.
@danalyser
Жыл бұрын
Glad to know that it was helpful Andres! :)
@shivsharma4337
Жыл бұрын
Hi Danalyser- You teach me here how to fish. Respect as a teacher😃
@danalyser
Жыл бұрын
Glad to know that!! Thanks for your lovely comments! 😊😊
@kesienaoghoma828
2 жыл бұрын
This was so helpful. Thank you for the video
@danalyser
2 жыл бұрын
So glad you find it helpful Kesiena ! :)
@scabral20
5 ай бұрын
Excellent
@mindspectre1
4 ай бұрын
This solution totally saved me. Thanks!
@danalyser
3 ай бұрын
Great to hear!
@mhdasfan13
6 ай бұрын
Thank you Dana! Saved me a lot of time and energy from adding a new dataset T_T
@danalyser
6 ай бұрын
Glad it helped! 😊
@scottjohnston7372
2 жыл бұрын
Thank you!
@danalyser
2 жыл бұрын
You’re welcome!
@stkuo612
2 жыл бұрын
Hi, thanks for the video!
@danalyser
2 жыл бұрын
You’re very welcome Steven! I’m preparing some videos to be back to KZitem. Feel free to suggest new videos to me that you’re interested to see! Have a great day🤓
@mahamohan1
Жыл бұрын
Fantastic. Crisp explanation ❤
@danalyser
Жыл бұрын
Glad you liked it! 😇
@thapelokhantsi8738
2 жыл бұрын
Exactly what i am looking for, thanks🙂👌
@danalyser
2 жыл бұрын
Cool! You're so welcome Thapelo, glad that it was helpful to you :)
@Movsattor
2 жыл бұрын
Thank you! this saves me some hours of reseach :)
@danalyser
2 жыл бұрын
Glad to hear this Jorge! :D SL
@Mariel7191
2 жыл бұрын
Seriously, thanks a lot :)
@danalyser
2 жыл бұрын
You're so welcome Mariel ! :) Feel free to share this with anyone that'd find this video useful ⭐️
@danalyser
2 жыл бұрын
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. 😢
@user-lw7te9ul1r
Жыл бұрын
HI THANKSSS!!
@thitipunchaichewanon7710
2 жыл бұрын
thanks! I like your vdo :)
@danalyser
2 жыл бұрын
You’re welcome! happy that you enjoy these videos 😁
@user-ds1qq4fq4c
Жыл бұрын
congrats for your channel
@danalyser
Жыл бұрын
Thanks alot!! 🙏🏻
@annalitvinenko8116
2 жыл бұрын
This is a great hack! I use it to calculate % by column in a table
@danalyser
2 жыл бұрын
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! 🙌🏻
@javierfito5077
Жыл бұрын
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)
@danalyser
Жыл бұрын
Great job! So happy that this video helps solve your problem! 🎉 Thanks for sharing, appreciate it! 💛
@mohdzulfadhlimj9662
2 жыл бұрын
This video really help me to solve my current problem for my dashboard....thanks ya...
@danalyser
2 жыл бұрын
Hey! You're most welcome! Good to see you around :) Just curious, are you also working as data professionals? :)
@mohdzulfadhlimj9662
2 жыл бұрын
Not really... but my work deal with data...and still learning thru youtube...
@danalyser
2 жыл бұрын
@@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 😊
@jannikadler7922
Жыл бұрын
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))
@danalyser
Жыл бұрын
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 :)
@cobbstopper
10 ай бұрын
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?
@SudarshanShrikantAtwe
2 ай бұрын
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?
@banjiaderibigbe1415
Жыл бұрын
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.
@EveningRepublic
5 ай бұрын
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!
@willweidman
2 жыл бұрын
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?
@danalyser
2 жыл бұрын
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
@satrackinformes408
2 жыл бұрын
Hi! Thank you for the video, i have other problem, i need to do a Sum Distinct, you know how to do it?
@danalyser
2 жыл бұрын
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.
@user-ng5sf7jz9c
Жыл бұрын
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.
@anggitaprameswari6116
8 ай бұрын
why is there no add field in my display?
@adiversdiary
11 ай бұрын
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.
@NomadPinoyRider
6 ай бұрын
Yes, you can use Case When, For example SUM(CASE WHEN Reporting Year = 2022 THEN Cost ELSE 0 END)
@DavideBarbatoo
2 жыл бұрын
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 :)
@danalyser
2 жыл бұрын
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.
@danielbarrett7432
2 жыл бұрын
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
@danalyser
2 жыл бұрын
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
@atkhthply
2 жыл бұрын
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!
@danalyser
2 жыл бұрын
Thanks again Atikah, nice talking to you earlier and hope to see you around more ! Happy learning 😊
@atkhthply
2 жыл бұрын
@@danalyser Thanks for the help! It’s also great talking with you😊😊
@SachinKumar-pi8sr
7 ай бұрын
can we do sum_distinct in Looker
@danalyser
6 ай бұрын
you could with the similar concept shared in this video, you may feel free to check out this SUMIF tutorial: kzitem.info/news/bejne/yaGEvJuGo5yge3Y&lc=Ugxmu1_SrFy413J6opp4AaABAg
@mikedechelsea
2 жыл бұрын
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?
@danalyser
2 жыл бұрын
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
@mikedechelsea
2 жыл бұрын
@@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.
@danalyser
2 жыл бұрын
@@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 :)
@dstorm2007
2 жыл бұрын
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
@danalyser
2 жыл бұрын
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
@BetaTube22
2 жыл бұрын
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.
@danalyser
2 жыл бұрын
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 😊
@gabriellourenco6136
Жыл бұрын
Hello, @@danalyser, how are you? Could you explain to me how I could solve this problem, please?
@gabriellourenco6136
Жыл бұрын
Hello, Leo, did you find any way to solve your problem? Could you help me? I'm in the same situation right now
@danalyser
Жыл бұрын
@@gabriellourenco6136 hi thank you for asking! I’m doing good. The video is already up in my channel: kzitem.info/news/bejne/p3hquGpnhWN3moI
@gabriellourenco6136
Жыл бұрын
@@danalyser Thank you!!! You're awesome!
@banjiaderibigbe1415
Жыл бұрын
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.
@danalyser
Жыл бұрын
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? :)
Пікірлер: 95