I have built a dynamic time interval with dynamic date selection parameters. I need to rank customers per the dynamic time interval revenue DESC, when I filter customers’ dimensions all of them except for one should not have an impact on the rank function. Any suggestion how to achieve this?
/*Dynamic Net Revenue Current*/
ifelse(
${PeriodStarting} = ' YTD' AND dateDiff({calendar_date}, ${dateselector}, "YYYY") = 0 AND {calendar_date} <= ${dateselector} , {net_revenue},
${PeriodStarting} = ' QTD' AND dateDiff({calendar_date}, ${dateselector}, "Q") = 0 AND {calendar_date} <= ${dateselector}, {net_revenue},
${PeriodStarting} = ' Trailing three months (T3)' AND dateDiff({calendar_date}, ${dateselector}, "MM") >= 0 AND dateDiff({calendar_date}, ${dateselector}, "MM") < 3 , {net_revenue},
${PeriodStarting} = 'TTM' AND dateDiff({calendar_date}, ${dateselector}, "MM") >= 0 AND dateDiff({calendar_date}, ${dateselector}, "MM") < 12 , {net_revenue},
${PeriodStarting} = ' Current Month' AND {calendar_date} = ${dateselector}, {net_revenue},
0
)
rank(
[sum({Dynamic Net Revenue Current Period})DESC ],
[{region_reporting_name}]
)
The partition in the Rank does not seem to work specially when I select TTM, QTD, YTD time intervals.
Hi @Shwana , welcome to the QuickSight community. Can you please explain this with an example? “when I filter customers’ dimensions all of them except for one should not have an impact on the rank function”. Your rank function includes {region_reporting_name} as the partition, is this your customer column or is the customer dimension a different column? Can you please provide a sample layout of your visual too?
Hi Salim, There is a customer dimensio. customers can report revenue in multiple regions_reporting_name. when I filter regions_reporting_name. However, customers always belong to one segment. (e.g. when I filter toview the top 10 customers for “ARN-Europe” region, and I select “Segment filter = ISV” customers, I would like to see what is the rank of that customers across total region reporting name, instead of seeing 1 through 10 ranks for the ISV customers. the top ISV customer is the highest revenue in its category, but probably ranked #41 across all customers from all segment within the total regions_reporting_name dimension. I want to see rank #41 instead of #1).
Hi @Shwana,
It’s been awhile since last communication on this thread, were you able to find a work around for your case or are you still in need of further assistance?
In your dataset, do you have one field for sales or are there separate fields for sales in each region?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Hi Brett, I have not still find a work around for this issue. The dataset has 27 regions in one field (column) and each region has 5 sales segments (this is one field or columns).
What I am trying to achieve is by selecting a regions, users can specify if they want to rank the customers for the entire regions across all sales segments or they can filter only one sales segment in one region, and see how the customers are ranked in the region level for their segment.
Hi @Shwana,
One quick suggestion, when responding to a message, make sure to include the @ followed by username so that the person you’re replying to gets notified about the response!
In regards to your case,
What if you tried this for your ranking format instead:
denseRank(
[sum({Dynamic Net Revenue Current Period}) DESC ],
[{region_reporting_name}],
PRE_AGG
)
Hi @Shwana,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
