Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I've searched in the forum for this topic and there are a lot of answers but I still can't figure out what am I doing wrong.
I have a table with the following data. This is just a sample, my data spans across several Year-Month, OwnerLevel and OwnerDomain
IncidentId | Year-Month | OwnerLevel | OwnerDomain | DispatchTimeSec |
1 | 2023-11 | 1L | TX | 300 |
2 | 2023-11 | 1L | TX | 60 |
3 | 2023-11 | 1L | TX | 900 |
... | ... | ... | ... | ... |
I'm putting this data in a Pivot Table with Year-Month being the top level, then OwnerLevel and then OwnerDomain.
As my measures I have:
A column with the value for the 75th percentile of the DispatchTimeSec, for which I'm using the formula: =Fractile(DispatchTimeSec, 0.75)
A 2nd column where I want to count the number of IncidentId where the DispatchTimeSec is below the value defined in the 1st column.
The first column is being calculated correctly but I can't figure out how to count the volume of IncidentId that are on that percentile.
Initially I was trying to count it with the following formula:
=Count({<DispatchTimeSec = {"<=$(=Fractile(DispatchTimeSec, 0.75))"}>}DISTINCT IncidentId)
But I found out that Set Analysis is dimension insensitive thus causing the problem I was having that I only got the correct count once I filtered the pivot table by all 3 dimensions.
How can I count the volume of IncidentID correctly in a way that it works on all dimensions, whether I'm looking at the lowest level (Year-Month+OwnerLevel+OwnerDomain) but also in the totals for Year-Month and Year-Month+OwnerLevel?
P.S. After some more tests I found out that there was a better formula to calculate the percentile for each set of dimensions with it being:
=If(Dimensionality()=3,Interval(Aggr(Fractile(DispatchTimeSec, 0.75), [Year-Month], OwnerLevel, OwnerDomain)/86400, 'hh:mm:ss'),
If(Dimensionality()=2,Interval(Aggr(Fractile(DispatchTimeSec, 0.75), [Year-Month], OwnerLevel)/86400, 'hh:mm:ss'),
If(Dimensionality()=1,Interval(Aggr(Fractile(DispatchTimeSec, 0.75), [Year-Month])/86400, 'hh:mm:ss'),
If(Dimensionality()=0,Interval(Fractile(DispatchTimeSec, 0.75)/86400, 'hh:mm:ss')))))
However I still cannot do a proper count of the volume of IncidentId which DispatchTimeSec is below the value in the percentile
I finally got it working following the formula I provided in my last reply.
This is what worked for me:
If(Dimensionality()=0, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL DispatchTimeSec, 0.75),IncidentId),IncidentId)),
If(Dimensionality()=1, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month]> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month])),
If(Dimensionality()=2, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month], OwnerLevel> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month], OwnerLevel)),
If(Dimensionality()=3, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month], OwnerLevel, OwnerDomain> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month], OwnerLevel, OwnerDomain)),
))))
I would create a new 'Flag' field by script. So for example you have in script this:
now you can count incidents by using: Count{$<Flag_Fractile_75th={1}>}IncidentID)
I've omitted this piece of info for simplicity sake but I'm working with bridge tables, so the Year-Month is actually coming from a different table, I just explained the way I did because that's the end result when I put all the info I need into a table.
Bottom line is I was really looking into an expression that allows me to do this calculation.
After searching some more I found this post:
Which contains an expression similar to what I want but this one is to calculate the average of the times that are below a certain percentile:
If(Dimensionality() = 0,
Avg({<Completion_Time = {"<$(=Fractile(Completion_Time, 0.75))"}>}Completion_Time),
Avg(Aggr( If(Completion_Time < Fractile(TOTAL <Work_type> Completion_Time, 0.75), Completion_Time) , Completion_Time, Work_type)))
Unfortunately I cannot get this working to count the number of IncidentId that are below the value of a certain percentile
Maybe it is possible to do it in a expression, but performance wise it would be better and easier to do it in script. Without the data and QVF it is hard for me to give you the exact expression.
I finally got it working following the formula I provided in my last reply.
This is what worked for me:
If(Dimensionality()=0, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL DispatchTimeSec, 0.75),IncidentId),IncidentId)),
If(Dimensionality()=1, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month]> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month])),
If(Dimensionality()=2, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month], OwnerLevel> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month], OwnerLevel)),
If(Dimensionality()=3, Count(Aggr(If(DispatchTimeSec <= Fractile(TOTAL <[Year-Month], OwnerLevel, OwnerDomain> DispatchTimeSec, 0.75),IncidentId),IncidentId, [Year-Month], OwnerLevel, OwnerDomain)),
))))