Sharing with you calculated fields I created for column ’ Recommendation type’ in the above table
The logic here is to first calc the running sum of ‘csm_time_spent_assumption’ with descending order by parameter $successmeric. And then compare #NoCSM with customer’s running total to get recommendations per each of the customer_. running total no. of headcount_t1
runningSum
(
max({csm_time_spent_assumption})
,[${SuccessMetric} DESC ]
)/100 Recommendation Type
ifelse({running total no. of headcount_t1}<${NoCSM},‘Maintain or Add’,
{running total no. of headcount_t1}=${NoCSM},‘recommended_allocation_model’
,{running total no. of headcount_t1}>${NoCSM},‘Remove or Keep No Coverage’,null)
Users then asking if I can provide an overview data like screenshot below:
I will need to have 2 columns to provide aggregated data for customers_ with different recommendations.
I am encountering 2 issues.
I got error ‘Custom agg are not allowed as dimension’ when I tried to use Recommendation Type as column.
I also tried to use ifelse to calculate how many customers_ under above recommendation type.
But the number looks wrong as all running total is less than the $NoCSM. How can I get a pre agg, running sum?
@Cindy Thank you for taking time to describe your use case and ask. It would be helpful to share a sample dataset that you plan to use for this scenario. This will help to meet your use case and address the errors along the way.
Thank you so much for your reply @DeepakS ! Logic: We want Users want to choose if they want the running sum of time spent assumption with decending order of ROI or Incremental Revenue, I created a parameter and caculated field(success Metric) to achieve that. After we got the running sum then /100 to calculate how many headcount users will need. Then compare with a user input value (parameter $NoCSM), base on that, we determine which recommendation to provide to that customer(SFDC account) Issues: My current logic is working fine when I have sfdc customer id in the field to provide detailed info to users. But I CAN NOT create a summay table (ex: How may sfdc account in different recommedations, what is the avg incrementaal revenue and ROI). Ex below:
Calculated field
Success Metric
ifelse(${SuccessMetric}=‘Incremental Revenue’,{incremental_revenue_csm},${SuccessMetric}=‘ROI’,{roi_csm},NULL)
Rank (order) the Success Metric
rank([max({Success Metric}) DESC])
Running Sum -‘running total no. of headcount_t1’
runningSum(sum({csm_time_spent_assumption}) ,[ {Rank of Success Metric}ASC],)/100
I have an excel file to better explain that. But I just noticed that excel file is not supported to upload. I am a AWS internal customer, please let me know if a file with data sample is easier for you. we can connect in slack. Thanks for your help!
@Cindy, thank you for sharing the details. Challenge with this use case is that it requires to use an aggregated results to create a row level evaluation. You may need to push the running sum aggregation to underlying database level and then use that aggregated dataset in Quick Sight to achieve the results. I will send you an offline email.
