How to use set analysis to select records excluded from the aggregation scope in a chart???

diagonjope — Mon, 20 Dec 2021 21:05:46 GMT

Greetings!

I am trying to use the E() function to select excluded records for the current aggregation scope in a regular table chart and I am wondering if someone else has run into issues similar to what I describe below. The Purpose of the char is to have a report containing Order, Product & Price dimensions as well as a measure containing the avg price of the same product in all other orders (that is, not considering the current one). Think of it as a naive price anomaly detection calculation.

I initially tried to use the E() function to have the calculation done on other Orders, as in Avg({$<Order=E(Order)>} Price), but, as can be seen in the image above, it didn't work in this visualization. However, it works when a visualization does not include Order in the aggregation scope and one selects an Order via the Sense UI (as shown in the second table below). The problem with this second approach is that this would require the user to select one order at a time via the Sense UI. My understanding is that this happens because the Order dimension is in the aggregation scope of the chart above, but not in the second chart below. I also tried to use different options for the field selector for Order (such as Order = P({1} Order) - Order), but none of them worked.

Since I just needed to calculate an average of the same products in other orders, I ended up using the following expression (sum({$<Order=>} TOTAL<Product> Price) - Price ) / (count({$<Order=>} TOTAL<Product> Price) - 1), which sort of solves the problem at hand. However, it seems to me that there must be better and easier ways to do this, since getting the proper results would require a very complex expression in other situations, such as for a requirement to calculate if a price falls within a standard deviation of the prices for the same product in other orders, or some such.

So, my question is: using set analysis, how does one use the current value of dimensions in the aggregation scope of a chart to select records that have dimension values completely excluded from at least a portion of that current scope?

I am attaching a test QVF in case anyone wants to play with it.

Please advise.

Cheers,

++José

topic How to use set analysis to select records excluded from the aggregation scope in a chart??? in App Development

How to use set analysis to select records excluded from the aggregation scope in a chart???