Hi Rajiv,
the cleanest way would be to add the information about national holidays to your data model (for example by adding a holiday flag to the master calendar per state) and then use this information to filter the data to do calculations on. Thus, you could state "Removing data points for national holidays" and everybody could accept or cry out, but knows what's going on.
I think you don't want to do that.
Hm, there are a lot of different criteria to label a data points as "outlier", like percentage of average value, distance to average by standard deviation, min/max, percentile etc.
Each criteria is somewhat abitrary and must be set "by experience".
In Rob's QlikView cook book, there is an example for some of these methods, I recommend having a look into that.
I also created an example that uses 20% of the average weekday value as threshold to determine if a certain datapoint should pass (and I do that only once, not iterative).
In attached sample, I created a simple data model for weekday values and tried to create some outliers also.
The resulting formula to calculate the corrected average (with above defined average) looks like
=sum(if(aggr(sum(Value),Date) >= aggr(NODISTINCT sum(Value) /count(distinct Date), Month, Weekday) /5 , aggr(sum(Value),Date))) /
count( Distinct if(aggr(sum(Value),Date) >= aggr(NODISTINCT sum(Value) /count(distinct Date), Month, Weekday) /5 , Date))
Pretty awful (and some of this is due to the special average we use) and possibly one can simplify that.
I use this just for a demonstration and playground, if you want to use this, one could also think about putting some of these stuff in the script.
Hope this helps,
Stefan
