topic Re: Double AGGR Function in a Table in App Development

Double AGGR Function in a Table

spjuza — Thu, 10 Nov 2022 16:06:38 GMT

I have a table with a variety of costs and I'm needing to get the 50th Percentile Cost within a feature. As I understand it, I will need to do two aggregations, one to get the total amount spent per product and then one to get the 50% cost within the feature.

Sample data table:

Product ID|Feature 1|Quanity|Spend

1|1|100|$1000

1|1|100|$1500

2|1|20|$220

So the results would look like this:

1|1|100|$1000|$10|$12.5|$11

1|1|100|$1500|$15|$12.5|$11

2|1|20|$220|$11|$11|$11

...

I have the code for how to get the Average cost per product, that was easy.

Fractile(aggr(sum([Spend])/sum([Quantity]),[Product ID]),.50)

However, trying to get that into a second aggregation has proved tricky. Wrapping everything in an additional Aggr doesn't return the expected values.

Fractile(aggr(aggr(sum([Spend])/sum([Qty]), [Product ID]),[Feature 1]),.50)

Thoughts on how to proceed?

EDIT: Someone at work pointed out that one of the issues is that you are overriding dimensions to show other data and suggested to use the Total function, but still not entirely sure how to go about that.

Re: Double AGGR Function in a Table

deepanshuSh — Thu, 10 Nov 2022 16:43:39 GMT

Maybe this could work, You can add the brackets and fields based on your requirement.

Fractile(Aggr(Sum(Total(aggr(sum([Spend])/sum([Qty]), [Product ID])),[Feature 1])),.50)

My suggestion would be to use variables rather than the direct equations.

Re: Double AGGR Function in a Table

spjuza — Thu, 10 Nov 2022 17:39:00 GMT

Unfortunately, this didn't seem to work. It was also missing one of the dimensions within one of the AGGR commands, but whether I put in Product ID or Feature 1, it didn't matter.

Re: Double AGGR Function in a Table

vinieme12 — Fri, 11 Nov 2022 03:14:32 GMT

as below

temp:
load *,recno() as ID inline [
ProductID|Feature 1|Quanity|Spend
1|1|100|$1000
1|1|100|$1500
2|1|20|$220
]
(delimiter is '|');

exit Script;

Dimensions

ID, ProductID,Feature1

Measures

Quantity = Sum(Quantity)

Spend= Sum(Spend)

Average Cost per Product ID = Sum(Spend)/ Sum(Quantity)

50th Percentile within Product ID = Fractile(TOTAL <ProductID> Spend/Quanity,0.5)

50th Percentile within Feature = Fractile(TOTAL <[Feature 1]> Spend/Quanity,0.5)