Here’s the condition I need Top 1% of total count of data which is done by max(wait_time). Now calculating the 1% and adding + 1 to find the position ( It would be the position of the required Wait Time and consider this Time. E.g. 1% of 1539 which is 15.39 approx 15 and sorting that to on largest to smallest on wait_time. Since ignoring the first 15th longest wait_time and have to look at 16th call and that is reported as longest wait_time. So I wanted to show that 16th value as max wait_time.
Can anyone help me to get this metric field.
All Calculation I have performed what changes I can make now? Count of Contact ID = 0.01*count(contact_id) Max of Wait Time = max(wait_time) Max of Wait Time (Min) = max(wait_time/60)
I think I am a little confused about how you are calculating the Top 1%. I will try to explain the solution to this in the way that I think will work best.
If you want to avoid displaying a certain percent of the max wait times, what you could do is utilize the denseRank function for max Wait Time to give a ranking on either Ascending or Descending values. Then depending on how you are calculating your percentage, I’ll utilize the function you provided above of Count of Contact ID = 0.01*count(contact_id), you could specify the number that you want to filter out.
If you are getting a value like 15 that you mentioned above, then you could filter out ranks 1-15 utilizing a calculated field, to just shows calls from 16 on. You will need to change the Count of Contact ID calculation to a countOver aggregation instead of count so it will work with denseRank. It would look like this: Count of Contact ID = 0.01*countOver({contact_id}, [], PRE_AGG)
Then you can check it against the denseRank function like this: ifelse({denseRankCalc} >= {Count of Contact ID}, {contact_id}, NULL)
denseRankCalc= denseRank([{Max of Wait Time} DESC], {Count of Contact ID})
I’m still in doubt thought because I have show Maximum wait time which I have calculated as max(wait_time) and it’s top 1% of which i.e. row 15th of Column wait_time is Last entry of Top 1% which is sorted in descending order by maximum of wait_time and I have to ignore that 15th row and take the 16th row wait_time which is considered as maximum wait_time of 1% i.e. wanted that calculated field created. Is that possible and hope you have got my point, what I was trying to explain?
@DylanM The below screen shot will clearly give you what I exactly needed. Total Contact is total count of data and 1%_TotalCount is 1% of Total Contact i.e. 1% of 5697=57 and Nth_indexofWaitTime is 58th rank (red marked) which we need by indexing that row wait time, currently stuck on that. Please help on this to wrap it up. Thanks in Advance!
Hello @vinay_revankar, did you update your Count of Contact ID function based on my suggestion above? Turning it into a LAC-W function should allow direct comparison with denseRank, I can post the field again below: Count of Contact ID = 0.01*countOver({contact_id}, [], PRE_AGG)
You should still be able to display the calculations as well. So is the main issue, that you are wanting to show 58 when the 1% of total is equal to 57? You can just have another column as a calculated field that looks like this: Nth_indexofWaitTime = round(0.01*count(contact_id)) + 1
Then if you want it to filter the KPIs, you can use a filter action to filter the visual by one of the identifiers on your table to give you that value. That is my best thought on how to accomplish what you are looking for.
Hello @vinay_revankar, also, I noticed you resposted the last portion of this question. I will archive this topic. Will you edit your new post and link to this question as well to include relevant information? That will help us guide you towards a solution. Thank you!
