find the entry =< the 25th percentile

[edwin]

hi,
im going out of my mind with this one and i think i know why, i just dont know how to fix it.
i have a data set that has 3 columns and a measure.  this is the sample data:

Geo	Quarter	SomeDimension	Sum(Measure)	25th%
SomePlace	Q1 2022–2023	D	27	37.75
SomePlace	Q1 2022–2023	C	30	37.75
SomePlace	Q1 2022–2023	A	61	37.75
SomePlace	Q1 2022–2023	F	76	37.75
SomePlace	Q1 2022–2023	E	87	37.75
SomePlace	Q1 2022–2023	B	99	37.75
SomePlace	Q2 2022–2023	C	14	16
SomePlace	Q2 2022–2023	D	14	16
SomePlace	Q2 2022–2023	E	22	16
SomePlace	Q2 2022–2023	B	49	16
SomePlace	Q2 2022–2023	F	72	16
SomePlace	Q2 2022–2023	A	80	16
SomePlace	Q3 2022–2023	B	20	53.5
SomePlace	Q3 2022–2023	F	47	53.5
SomePlace	Q3 2022–2023	C	73	53.5
SomePlace	Q3 2022–2023	D	75	53.5
SomePlace	Q3 2022–2023	A	89	53.5
SomePlace	Q3 2022–2023	E	98	53.5

the 25th%ile is aggr over Geo and Quarter - so 37.75 for Q1, etc..  finding the %ile is easy but the ask is to find the 3rd dimension and the SUM at or immediately below the %ile.  for this specific example, we are looking for the following:

we should get SOMEDIMENSION C for Q1, C and D for Q2, F for Q3.

if the %ile were an exact match its easy to use an if statement, however finding the max(sum(measure)) that is less than the % aggr by Geo and Quarter is over my head.  if it were a single Geo and Quarter, its easy to add the %ile in a set analysis phrase however, if multiples are selected, it doesnt work.

any help is appreciated.

here are y expressions:
Sum:

Sum(Measure)

25th%:

		aggr(nodistinct
			Fractile(
				aggr( 
		    		sum(Measure),
		        	Geo, 
		        	Quarter,
                    SomeDimension
		    	),
		    	0.25
			),
		    Geo, 
		    Quarter
	    )

[edwin]

to add on, this is what i was saying when a specific quarter is selected, i can find the max within the 25th%:

usingthis expression:

aggr(nodistinct max(aggr(sum({<SomeDimension={"=Sum(Measure)<=aggr(nodistinct Fractile(aggr(sum(Measure), Geo, Quarter,SomeDimension),0.25),Geo, Quarter)"}>}Measure), Geo, Quarter, SomeDimension)),Geo, Quarter)

however, when evaluating over more than 1 group, it breaks

[Kushal_Chawda]

@edwin  try below

=max(total <Geo,Quarter>aggr(if(sum(Measure)<=aggr(nodistinct Fractile(aggr(sum(Measure),Geo, Quarter,SomeDimension),0.25),Geo, Quarter),sum(Measure)),
Geo, Quarter,SomeDimension))

[edwin]

hi @Kushal_Chawda , thanks for the switf response, it does work.

however do you think there is a set analysis expression that will work for this?  the actual data is big and i prefer not to use an IF statement.

[Kushal_Chawda · Accepted solution]

@edwin  you could do it, but you need to have primary key in your table, if you don't have you need to create custom primary key. Creating key using Combination of your report dimension (Geo,Quarter, SomeDimension)  could work. If you already have primary key in your table then you can use it directly, but I have created it in script using Rowno() function.

LOAD RowNo() as primary_key,
    Geo,
    Quarter,
    SomeDimension,
    Measure
FROM source

now you can use this primary_key in your set expression like below

=max(total <Geo,Quarter>aggr(sum({<primary_key={"=sum(Measure)<=aggr(nodistinct Fractile(aggr(sum(Measure),Geo, Quarter,SomeDimension),0.25),Geo, Quarter)"}>}Measure),
Geo, Quarter,SomeDimension))

[edwin]

Thanks Kush