Count of records compared to the average

[znhnm]

Hi,

I have a start and end date in my data set.  I calculated a new item called "number of days" as the number of days in between these two dates. I also calculated the average number of days using the median which is 59. Now, I would like to count how many of my records are above the average number of days and how many are below the average. What would be the calculated item formula for it?  For example, assuming I have these 5 records on my dataset:

Number of Days 
110
100
45
55

25

Then, my new calculated item "above average" would have the value 2. and My new calculated item "below average" would have the value 3. So, I could create a key value objects using these values as the measure. 

[Sam_SAS]

Hello,

This is a tricky one. I think you will need to precalculate the median value as part of your data preparation.

VA expression syntax does not like to compare numerics to aggregated measures, so you cannot simply calculate the median (which returns an aggregated measure) and compare that to the number of days.

So if you precalculate the median (using PROC MEANS, for example) as a regular measure column, then you can create an expression based on that.

You could make your two calculations as follows:

Above Average:

IF ('Number of days'n >= 'Median'n) RETURN 1 ELSE 0

Below Average:

IF ('Number of days'n < 'Median'n) RETURN 1 ELSE 0

You can now apply Sum aggregation to these to get the count.

As always, there might be a better, simpler solution I'm not thinking of.

Let us know if that helps,

Sam