I am trying to solve for a monthly calculation that can be rolled up to quarter and year. Here is the problem.
Also, we may have to talk about this one on the phone but i’ll try to explain it here.in this screen shot the following fields are being calculated as: Accounts with Activities:
distinct_countIf({GS_Company_Id},{Type_Name}=“Email” OR {Type_Name}=“Meeting” OR {Type_Name}=“QBR” OR {Type_Name}= “Live Consultant” OR {Type_Name}= “Onboarding” OR {Milestone_Type_Name}=“QBR”)
Number of Companies:
distinct_count({GS_Company_Id})
% of Accounts with Activities:
ifelse({Accounts with Activities}/{Number of Companies}>=1,1,{Accounts with Activities}/{Number of Companies})` My goal is to get to a figure that captures the % of a CSMs accounts that they had at least 1 activity with each month. I have a pretty good solution until you try to roll it up past 1 month. What happens is that Quicksight will take a CSMs 4/5 accounts with activities in Apr and 4/5 in May and 3/5 in Jun. That calculates correctly per month. However, when you calculate it by quarter or year it will take those same 5 accounts and potentially return 100% because they hit all 5 accounts in that quarter but not necessarily in every single month of the quarter.
Secondarily, My number of companies won’t work because there would need to be a row of data indicating that there was a distinct company under that person’s control. My challenge with Quicksight is that you cannot reference multiple datasets from a visual like you can in PowerBi.
Thanks for the resources. I am still new to Quicksight so I’m not familiar with all of the functions. I’ll try the distinctCountOver function.
The dates are being rolled up through a relative date filter. I have a KPI card that shows the % of accounts with an activity for a given range defined by the filter control. That is probably the source of my issue and I’m unsure of how the PRE_AGG will work in this scenario.
I haven’t looked at the SQL functionality documentation yet but I have a feeling that will be the route to go. My thinking is that I can create a table that calculates that metric by month and CSM and add that dataset to my dashboard. Is that correct?
I have not figured out how to make the pre_agg work for this solution. This is what I have so far:
distinctCountOver({Company_GSID},[{CSM_GSID},{Month of Date}],PRE_AGG)
My thinking was that I want it to calculate the number of unique accounts with activity by CSM and by month. However, when I run this calculation and compare it to normal Distinct Count If, the result is way off and I am unsure of why.
It should calculate the dataset and count over the CSMs and the month of the activity but I am unsure of how i get to the result it is giving me. I want it to give me the sum of the months listed in the first table but it’s giving me a smaller result. What am I doing wrong?
Part of the issue could be coming from the relative date filter. I think a lot of the problem will be solved by using the custom SQL to create a single table with the a month over month metric.
Have you tried using the custom SQL functionality yet?
Hey @bmike4 , sorry for the late reply. Were you able to find a solution for this or are you still working on it?
Ultimately, yes you are probably being blocked by the file being a CSV. You could try using Athena or joining your two datasets that you want to use in the single visual.
