Fractile/percentages

[BN]

Hi all. I am hoping to get some solution on this. 

 I am trying to calculate fractile/quantiles for my data per year but for specific data category per year. If in my set analysis i add the category i don't get a result even though i get an ok . I tried with Aggr(sum but still no result.

1.

if(aggr(sum({$<data_category = {'GEN'}>}po_amount),year<=(Fractile( total (Aggr(sum({$<data_category = {'GEN'}>}po_amount),year, 0.25), 'Quartile 1',
if(aggr(sum({$<data_category = {'GEN'}>}po_amount),year<=(Fractile( total (Aggr(sum({$<data_category = {'GEN'}>}po_amount),year, 0.50), 'Quartile 2',
//iF (po_amount<=Fractile(Total po_amount, 0.75), 'Quartile 3',
'Quartile 4'))))))))))

2.

=if(sum({$<data_category = {'GEN'}>}po_amount), year<=Fractile( Total {$<data_category = {'GEN'}>}po_amount,0.25),'Quartile 1',
if(sum({$<data_category = {'GEN'}>}po_amount), year<=Fractile(Total {$<data_category = {'GEN'}>}po_amount,0.50), 'Quartile 2',
if(sum({$<data_category = {'GEN'}>}po_amount), year<=Fractile(Total {$<data_category = {'GEN'}>}po_amount,0.75), 'Quartile 3',
'Quartile 4')))

but i do get a result with this , 

//iF (po_amount<=Fractile(Total po_amount, 0.25), 'Quartile 1',
//iF (po_amount<=Fractile(Total po_amount, 0.50), 'Quartile 2',
//iF (po_amount<=Fractile(Total po_amount, 0.75), 'Quartile 3',
//iF (po_amount>=Fractile(Total po_amount, year, 0.75), 'Quartile 4'))))
//'Quartile 4')))

 Thanks in advance

[Or]

Perhaps I am misreading this, but it looks like you're comparing the sum of po_amount with the fractile of po_amount, which doesn't seem to make a lot of sense as one is an aggregation of all PO amounts and the other is the fractile of a single po_amount?

[BN]

Thank you for replying.  Basically i need to find the fractile/ Quartile of po_amount for the data category GEN, then find the outliers on the sum of PO_Amount for the data category Gen per year  and then calculate for the top 1% of outliers. I am working with a pivot table and the client wants to see outliers at the level of total spend per year not individual spend.

I am able to get the quartiles based on the full data without specifying the data category as 'Gen' like this 

//iF (po_amount<=Fractile(Total po_amount, 0.25), 'Quartile 1',
//iF (po_amount<=Fractile(Total po_amount, 0.50), 'Quartile 2',
//iF (po_amount<=Fractile(Total po_amount, 0.75), 'Quartile 3',
//iF (po_amount>=Fractile(Total po_amount, year, 0.75), 'Quartile 4'))))
//'Quartile 4')))

[BN]

Thank you for replying.  Basically i need to find the fractile/ Quartile of po_amount for the data category GEN, then find the outliers on the sum of PO_Amount for the data category Gen per year  and then calculate for the top 1% of outliers. I am working with a pivot table and the client wants to see outliers at the level of total spend per year not individual spend.

I am able to get the quartiles based on the full data without specifying the data category as 'Gen' like this 

//iF (po_amount<=Fractile(Total po_amount, 0.25), 'Quartile 1',
//iF (po_amount<=Fractile(Total po_amount, 0.50), 'Quartile 2',
//iF (po_amount<=Fractile(Total po_amount, 0.75), 'Quartile 3',
//iF (po_amount>=Fractile(Total po_amount, year, 0.75), 'Quartile 4'))))
//'Quartile 4')))

[Or]

I'm afraid I don't follow. Let's say you have three POs:

100

200

300

The median (0.50) is 200.

The sum is 600, and is never going to be smaller than the median (unless some values are negative).

Your formula, as described, is only ever going to work at a single-PO level because it's the same as writing Only(po_amount). "the outliers on the sum of PO_Amount" doesn't make sense to me in this context, since we're operating on a single-line level and nothing is being aggregated so there is no sum. Perhaps you could supply sample data and expected results of what you're trying to achieve?

[BN]

Hi, Thanks again. 

see attached sample data. This is what i have produced from that data in a pivot table

As you can see i have only calculated for the sum of PO_amount per year for the category GEN =sum({$<data_category = {'GEN'}>}po_amount). Client want to see the Quantile / percentage at the sum level per year not at the single po-amount and specifically for only Gen.

[Or]

I'm still not quite sure I follow, but from the sound of it, it seems like you're looking for a solution that involves using an internal aggr() to pre-sum the values, so perhaps something like

Fractile(aggr(sum({$<data_category = {'GEN'}>}po_amount),year),0.25)

Which, if I wrote it correctly and understood the requirement correctly, would get you the 25th fractile of the year values for each loc_descr (since that is your dimension). Might be missing a total in there somewhere, I'm not sure.

[BN]

Perhaps if I could get this to work then I can color the different values accordingly and that may solve the issue at one level atleast. But even then No matter how I tweak it, it is not  accepted in set analysis, can you take a look for me please. 

iF ({$<data_category = {'GEN'}>}po_amount<=Fractile(Total ({$<data_category = {'GEN'}>}po_amount, 0.25)), 'Quartile 1',
iF ({$<data_category = {'GEN'}>}po_amount<=Fractile(Total ({$<data_category = {'GEN'}>}po_amount, 0.50)), 'Quartile 2',
iF ({$<data_category = {'GEN'}>}po_amount<=Fractile(Total ({$<data_category = {'GEN'}>}po_amount, 0.75)), 'Quartile 3',
'Quartile 4')))

[Or]

If() does not accept set analysis... it's not an aggregation function.

[BN]

Thanks again. I think i am not explaining very well.

What I'm trying to do is show the fractiles of po_amount for the data category Gen  in quintiles.

I.e. I want to show the sum of po-amounts for data category Gen that are in the 0-25% range,  25-50% range, etc. Iam able to get the quartiles at one level but when i specify data category then i don't get a result. 

iF (po_amount<=Fractile(Total po_amount, 0.25), 'Quartile 1','Quartile 4')This works.

iF (sum({$<data_category = {'GEN'}>}po_amount)<=Fractile(sum (Total ({$<data_category = {'GEN'}>}po_amount, 0.25)), 'Quartile 1','Quartile 4')))This doesnt work