Visokio website     Downloads     Video tutorials     KnowledgeBase  
Automatic moving average field - Visokio Forums
Automatic moving average field
  •     Phillipa August 15, 2012 12:06PM
    Hi,

    I have just been asked to work out a moving average for quarterly no. of patients, which I have done quickly in a content view using the formula

    (SUBSET_SUM([All E Pat.], SUBSET([Quarter], "II Q 2011"))+

    SUBSET_SUM([All E Pat.], SUBSET([Quarter], "III Q 2011"))+

    SUBSET_SUM([All E Pat.], SUBSET([Quarter], "IV Q 2011"))+

    SUBSET_SUM([All E Pat.], SUBSET([Quarter], "I Q 2012"))) /4

    and then changing the dates for the next average.

    It's simple but not elegant. Is there any way to get an automatic field to update each quarter. I know that the format for the field "Quarter" is not a date format and probably needs to be to get an automatic recalculation, but am not sure what the best way is.

    I am pretty sure that I will have to caculate the rolling average for market share in the future, which would involve a much longer formula as we use aggregated data and appended files.

    Any ideas gratefully appreciated.

    Phillipa
  • 3 Comments
  •     paola August 16, 2012 11:56AM
    Hi Phillipa,

    You could do it by adding one more field [Order], that would number the quarters, 1,2,3,4,5...

    Formula

    IF([Order]<3,

    /*Value if true*/
    " ",

    /*Value if false*/
    (SUBSET_SUM([Value], SUBSET([Order], [Order]))+
    SUBSET_SUM([Value], SUBSET([Order], [Order]-1))+
    SUBSET_SUM([Value], SUBSET([Order], [Order]-2))+
    SUBSET_SUM([Value], SUBSET([Order], [Order]-3)))/4)

  •     paola August 16, 2012 1:01PM
    Please note Graph View has built-in graphical Moving average feature, so your table results should correspond to the graph values. You will need to aggregate on the [Order] field.
    Attachments
    		 QuarterMovingAve.iok 7K
  •     Phillipa August 16, 2012 1:24PM
    Hi Paola,

    I think I see what you are getting at. It will automatically calculate anything that has 4 quarters.

    However, it doesn't get round the fact that due to space restrictions one of the earlier quarters may be filtered out on the graph. If the first quarter is filtered out then the moving average will not contain that quarter!! But I still want to see the MA for the year.

    Using static values won't help either as sometimes we filter by region, institution and even therapy stage (1,2 or3).

    It seems like a catch 22 situation.

    Phillipa
Add a Comment

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories