Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Product ID|Feature 1|Quanity|Spend|Average Cost per Product ID|50th Percentile within Product ID|50th Percentile within Feature
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.
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)
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.
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.
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)