I have a dashboard I’m trying to put together. The idea being “out of all potential users what percentage have converted to using Client B primarily within the last Z time frame”.
I have a sample dashboard to look at an play with. I can correctly present my data in a tabular form per user but I can’t figure out how to properly sum the aggregate values to get the totals I need.
I’ve commented in my calculated fields to help understand what I’m doing with what. In the sample you’ll see “user1” and “user2” being counted as “converted”. I need to be able to show a percentage of how many users are converted out of all potential users for a given date (today or other date).
Ultimately I want to plot the percentage or total binned by day.
Hello @bmurphy88, welcome to the QuickSight Community!
First of all, thank you for taking the time to add this into Arena! This is a new feature, and I am excited to see someone taking advantage of it. I would say, the best way you will be able to achieve this is going to be through the use of the LAC-W function sumOver. I’ll link some QuickSight documentation to give you some more information on this:
Basically, what we will want to do, is use sumOver to get the total number of users converted to client B and the total number of potential users. If we use sumOver to calculate both, we will be able to divide them to get the percentage you want. It would look something like this: sumOver({Is Client B Converted}, [], PRE_AGG)/sumOver({Potential Conversion User}, [], PRE_AGG)
This will return the percentage based on the total number of users converted to client B based on the total number of potential users. Let me know if you have any issues implementing this, and I will be happy to guide you. Thank you!
First of all, thanks for teaching me I can leave the partition field empty. Didn’t think about that.
Second, I did try messing with sumOver but no luck. My calculated fields are already aggregates and thus using the PRE_AGG calculation level won’t work. Did I misunderstand you?
Hello @bmurphy88, my apologies for the delayed response! Do you have the ability to update the dataset through custom SQL? I think it is likely that moving some of the aggregations you are doing in QuickSight to the SQL on the dataset will likely be the best way to make this work. Then, if your Is Client B Converted and Potential Conversion User fields are no longer calculated fields, you will not have an aggregation issue when you try the sumOver calculation.
After looking at your calculated fields in Arena a little more, there might be one other alternative that could work without resulting to SQL. Instead of using countIf functions, which will result in errors when adding the output of that function in a sumOver, you can use ifelse statements to return ClientType and then count the returned value.
ifelse(
{Client Type} = 'Client B' AND
{event_date} >= addDateTime(-${LastZDays}, 'DD', ${TodayOrPreviousDate}) AND
{event_date} <= ${TodayOrPreviousDate}, {Client Type}, NULL
)
Then you can apply a custom filter to this field, select Does Not Include, and then either leave the text field blank or enter an impossible value, and select Exclude Nulls from the last dropdown. If you convert to ifelse statements, you could then use countOver or sumOver to get the values for the number of users converted to Client B over the total number of users.
Hmm I thiiiiink I can do something in the SQL? I’d have to try. But, I’ll also take a look at your suggestion for the change from countIf to ifelse. That makes a lot of sense. I’ll respond with my results in the end.
I can definitely switch my Client fields from using countIfs to ifelse and that definitely stops them from being an aggregate. Problem I’m now running into is in order to calculate % Client B Converted I’d still need to do a sumOver on an aggregate since I still need to sum then calculate a percentage of client usages. Am I stuck and just need to mess with the SQL? Seems like this is something ultimately simple and I’m maybe just complicating things.
Hello @bmurphy88, I completely understand. It can be easy to go down a rabbit hole when you start building out these calculated fields. As long as you maintain LAC-W functions for each piece of the % Client B calculation, you should still be able to make it work.
I’ll try to make an example. If you want the percent of converted users to Client B based on users that converted to B and A, you would want an ifelse statement that would determine A conversions and B conversions. Then, you could try this: sumOver({Client B Calculated Field}, [], PRE_AGG)/(sumOver({Client B Calculated Field}, [], PRE_AGG) + sumOver({Client A Calculated Field}, [], PRE_AGG))
This may require some changes depending on how you build out the ifelse calculations, like if you wanted to do distinctCountOver for the user IDs being returned for each Client you could substitute that for sumOver. I think we can get you to your expected output.
What I will say though, if you are comfortable working with SQL and building out datasets, that is often a way to manage this a little bit more clearly. It might be worth taking the time to make them that way to ensure any future calculations in QuickSight are easier to make. I’ve gone both ways with this, so that is up to your personal preference.
It’s probably worth it for me to learn both honestly. I don’t think I would need to be distinct on the users since I’m after tallies of Client usage and percentages there of.
Seems like the crux here is I definitely need sumOver({Is Client B Converted}, [], PRE_AGG)/sumOver({Potential Conversion User}, [], PRE_AGG) in the end. But, with the measures not being aggregates. Only way I could possibly think of getting those fields to not be aggregates would be some clever combination of filters.
I’ll also have another look at LAC-W functions since you mentioned them.
Hello @bmurphy88, it has taken me quite a while to become super comfortable with the LAC-W calculated fields and figuring out how to make them work together. With some time and practice, you will definitely start to gain a better understanding of how they interact with each other, and where the limitations are. I used to rely more heavily on SQL because that is what I was more confident with. The sweet spot is when you understand how to build your dataset with SQL to interact most efficiently with the calculated fields to best utilize both.
I am happy to give advice to assist any way I can though. If you have any questions when digging into the custom SQL for this dataset, please post a new topic in the community and feel free to link this question and tag me. I definitely think we can achieve your desired output.
Hello @bmurphy88, I will mark my above response as the solution for now. If you run into any remaining issues while implementing this or have a follow-up question, please let me know!
Hi. While I appreciate very much your help on this matter I’m un-marking it as the solution. This is more complex than appears and the discussed ideas have not proven themselves out yet. I’m still attempting to figure out a path forward given the knowledge you’ve shared here in this thread. But, my original problems still exist.
Hello @bmurphy88, have you tried to make some of the fields you are creating in calculated fields in the custom SQL query instead? I think it would be helpful to run some of the aggregations directly in the SQL, like connecting users to their particular client, so that you can simplify the aggregations needed on the QuickSight level. If you can provide some information about where in this process you are currently stuck at, I can try to lead you towards your expected solution.
I’m presently seeing how I might re-do some of my calculated fields to make things more manageable. I’m trying to make sure I understand exactly what it was you were alluding to above and to see if there’s any pre-filtering I can do on the data. I run into problems there though because I have to deal with two separate buckets of event_date (Last Z and last 2Z).
I haven’t tried tampering with SQL because I can’t even imagine how I’d make anything useful there for what I need. Since my ultimate problem comes at the very end when I have to start doing all these aggregations I’m not able to see how it would help me. Truth be told I’d have to go learn to how to do that anyway.
So in short, to answer your question. Still very stuck as per the beginning.
Hello @bmurphy88, I think part of the problem is that you are running aggregations in QuickSight to retrieve the data points like Is Client B Converted and Potential Conversion User, and then trying to run aggregations on those results to determine the values you are wanting to display. This is likely going to cause issues with the aggregations, errors like mismatched aggregations, which could create road blocks when trying to determine some of these metrics.
That is why I think trying to build some of these aggregations like Is Client B Converted within SQL would allow you to then determine more of these metric values without running into errors in the console. Creating the bins that you want to aggregate by in the dataset will allow for the aggregations in QuickSight to feel a lot more plug and play. Is there someone on your team with some SQL knowledge that could assist you in creating that dataset based on your expectations for the dashboard? I can send some helpful links with some functions that might be helpful, but guiding you through building custom SQL myself is a more in-depth project.
Hello @bmurphy88, I got together a list of some helpful SQL links. I’ll send them over below. After this response, I will go ahead and archive this topic. If you need further assistance with building out the dataset, or implementing it in QuickSight, please post a new topic in the community. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!
Select statements - the base of the query from your database. Ensures you are able to retrieve necessary values from your database.
Group by - Good to understand how your values will be grouped after the query and helps determine the number of rows that will be returned
Case when - this works like an ifelse statement. Allows you to check if a condition is met to determine the value that will be returned
Union/Union All - Super helpful for creating categories that may not exist in the database and splitting up the data you have into groups with where clauses
Joins - Left, Right, Inner, and Outer are all types of joins to link different tables/views in your database together
W3 Schools is a great resource to get information about many of the functions available in SQL. I hope this helps!