Counting volume of tickets that have DispatchTime below fractile

[angelompcunha]

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

[jochem_zw]

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)

[angelompcunha]

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:

Percentile-in-pivot-table 

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

[jochem_zw]

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.

[angelompcunha · Accepted solution]

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)),
            	))))