I’m hitting problem that calculate the calculated field and the non-calculated field.
I want to calculate cpa of advertisement.
So i made the below formula.
{advertisement_budget} / countIf({id[user]}, {utm_source} = “advertisement”)
But quicksight can not combine the calculated field and the non-calculated field.
How can i solve this problem?
Hello @yuuyake9191, welcome to the QuickSight community!
There are a couple of options to make a calculation like this work. What kind of visuals are you implementing the field on? If it is on a table that will partition the returned values how you are wanting, then we can try something like this:
{advertisement_budget}/ifesle({utm_source} = 'advertisement', 1, 0)
Another option would be to aggregate the advertisement_budget field depending on how many rows you want to be accounted for. Using sum for many rows you want to add up based on user ids or min if it is a single row, we should be able to ignore the errors:
Account for many rows = sum({advertisement_budget})/count(ifelse({utm_source} = 'advertisement', {id[user]}, NULL)
Account for one row = min({advertisement_budget})/count(ifelse({utm_source} = 'advertisement', {id[user]}, NULL)
Depending on your implementation, one of these options should get you the result you are wanting. I will mark this response as the solution, but please let me know if you have any remaining questions. Thank you!
Thank you @DylanM !
Seems {advertisement_budget}/ifesle({utm_source} = ‘advertisement’, 1, 0) well.
But the chart was something wrong…
the below are weird points.
① the data didn’t show all on the chart.
② the data was not calculated
③ calculated field does not display as “custom”(shown total, average, count etc…)
do you have any idea for those problem?
than you.
Hello @yuuyake9191, maybe we can try switching the calculations to LAC-W fields to resolve the problem. Something that would also help me resolve this for you would be if you added a demo version of this in QuickSight Arena and linked the shared dashboard with me in your next response. That way I could test out options to find a solution.
Let’s try switching to LAC-W fields. I’ll use the date field as the partition for the calculation:
Date Month = truncDate(‘MM’, {Date})
CPA = sumOver({advertisement_budget}, [{Date Month}], PRE_AGG)/countOver(ifelse({utm_source} = 'advertisement', {id[user]}, NULL), [{Date Month}], PRE_AGG)
Alternatively, you can try minOver if the advertisement_budget is the same across many rows across the month:
CPA = minOver({advertisement_budget}, [{Date Month}], PRE_AGG)/countOver(ifelse({utm_source} = 'advertisement', {id[user]}, NULL), [{Date Month}], PRE_AGG)
Then, when you add this to your field well, make sure you use the min aggregation on the field. That should return the value you are wanting for each month. I would recommend testing this in a table visual as well to see if the values are matching your expected values. Thank you!
Thank you @DylanM .
I tried your solution but it didn’t work well.
So I made dashboard as your suggestion.
I’m happy if you check.
Thank you.
Hello @yuuyake9191, can you explain what didn’t work well about my previous suggestion? Did you run into any error messages?
I would also recommend try adding some of those calculations into a table visual to see what is populating. Are you getting anywhere near your expected values?
Hi @DylanM .
I typed the below as your suggestion.
It got error pointing [{Date Month}] and I solved error to change other values.
But It didn’t work well.
What my goal is the below.
I recreate dashboad.
test test
【Goal】
・calculate google advisement cpa for done of user registration interview status
【Current Problem】
Note: you can see on the dashboard
・cpa is wrong(Dec 2023 displays 200,000 but should be 100,000. Jan 2024 displays 24,000 but should be 12,000)
Hello @yuuyake9191, I went through the Arena dashboard and found a few things. For the first chart you will see the calculations you created. When I set that aggregation on the field well to min, it was showing the values you expected for the first to data points.
Then, the 2nd line chart visual will show the values based on the calculation I created. The numbers seem larger than what you were expecting for some of the data points, but one of these options should provide the answer you were looking for. Let me know if you have any questions!
Hi @DylanM .
Finally I did it!
I really appreciate every your kindness.
After all calculation field was the below.
{google_ad} / sumOver(ifelse({utm_source} = 'google' AND {user_registration_interview_status_id} = 1, 1, 0),[{Date Month}],PRE_AGG)
Something different In production environment I guess.
Thenk you @DylanM !
Hello @yuuyake9191, thank you for following up. I am glad you were able to get the calculation to work as you expected!
