Set Analysis Help Needed

[us_enterprise_s]

Hi!

I am working on creating a set analysis expression and need some help or pointers from the experts.

1. I calculate a number, using the following expression: =avg(aggr(sum(IB_COUNTER),[%ENCRYPTED_NAME], [%IB_DEVICE_TYPE]))

2. What I wan to do next is calculate a MAX((aggr(sum(IB_COUNTER),[%ENCRYPTED_NAME], [%IB_DEVICE_TYPE])), but only for the values that meet the following criteria:

=fabs(Sum(TOTAL<[%IB_DEVICE_TYPE]> IB_COUNTER) / COUNT(TOTAL<[%IB_DEVICE_TYPE]> DISTINCT [%ENCRYPTED_NAME]) - avg(aggr(sum(IB_COUNTER),[%ENCRYPTED_NAME], [%IB_DEVICE_TYPE]))) > $(vOutliersThreshold) * Stdev(TOTAL<[%IB_DEVICE_TYPE]> Aggr(Sum(IB_COUNTER) ,[%IB_DEVICE_TYPE],[%ENCRYPTED_NAME]))

I think this could be done using indirect set analysis, but I am struggling with constructing the right expression.

GREATLY APPRECIATE ANY HELP!!

[sunny_talwar]

Would you be able to share a sample or some raw data and explain the expected output out of this sample?

[us_enterprise_s]

Sunny,

Please see the picture below.

I have color coded cells RED, when the value falls outside of Avg Value (first row) + 1 Std. Dev. These are outliers. Now, I would like to calculate the average value, for each Device Type, but only for cells that are green.

[sunny_talwar]

May be this

=Avg(Aggr(

If(fabs(Sum(TOTAL<[%IB_DEVICE_TYPE]> IB_COUNTER) / COUNT(TOTAL<[%IB_DEVICE_TYPE]> DISTINCT [%ENCRYPTED_NAME]) - avg(aggr(sum(IB_COUNTER),[%ENCRYPTED_NAME], [%IB_DEVICE_TYPE]))) > $(vOutliersThreshold) * Stdev(TOTAL<[%IB_DEVICE_TYPE]> Aggr(Sum(IB_COUNTER) ,[%IB_DEVICE_TYPE],[%ENCRYPTED_NAME])),

Sum(IB_COUNTER), [%ENCRYPTED_NAME], [%IB_DEVICE_TYPE]))

[us_enterprise_s]

Sunny, does not work.

If you look at the image attached, value returned for the first Device Type (CPAP, BiPAP) should be 14 (average of all values in the CPAP/BiPAP column, excluding 84, the outlier).

[sunny_talwar]

What does the above give you?

[us_enterprise_s]

Error. It does not return anything

[sunny_talwar]

Would you be able to share a sample qvf file?

[us_enterprise_s]

here it is

[sunny_talwar · Accepted solution]

Try this

If(Dimensionality() = 1, sum([Device Count]),

avg(aggr(if(fabs(Sum(TOTAL<DeviceType> [Device Count]) / COUNT(TOTAL<DeviceType> DISTINCT Customer) - avg(aggr(sum([Device Count]),Customer, DeviceType)))

<= 1*Stdev(TOTAL<DeviceType> Aggr(Sum([Device Count]) ,DeviceType,Customer)), sum([Device Count])),Customer, DeviceType)))