8 Replies Latest reply on Jan 15, 2014 7:20 AM by Nate Harriman

alternative to window average?

Hi, we have data by quarter, and can create a rolling five quarter average of measure 'x' via

window_avg(sum[x]),-4,0)

it seems that this will only work when five or more quarters are visible -

but ideally this formula/result would be available even when you are only working with the latest quarter - when you only have one quarter in the window. Is there perhaps an alternative to window_avg? regards

• 1. Re: alternative to window average?

I believe you can just remove the '-4,0' arguments from the calculated field, and use:

window_avg(Sum(x))

This will compute based on the dimensions in your view.  The -4, 0 was specified for 5 periods, but without it, we can compute based on the level of detail in our view.

Let me know if this helps resolve your issue--a packaged workbook is always most helpful in getting the best response you possibly can on this forum (.twbx file).

NOTE:  I misunderstood the issue the OP was describing, and this will not resolve the issue, but will leave this comment in place for anyone looking for clarification on WINDOW_ calc arguments.

• 2. Re: alternative to window average?

Nate,

You should have an option in this case.  When you edit the table calc, do you have a check box like this?

Regards,

Joshua

• 3. Re: alternative to window average?

Hi, thank you both - but it is not as though i want the result to be null if five quarters are not visible. The calculation can be done correctly on any tab which has five quarters or more - so really, the ability exists - is there a way to use this information, or make a five period average, when only (say, the latest quarter) is displayed in the tab? (maybe i need to have a bunch of quarters in the tab, and hide some of them, etc, so that only the latest quarter appears in the tab - or maybe there is a formula which does not rely on quarters that are visible in the tab, and can 'see' data for older periods.

• 4. Re: alternative to window average?

Nate,

I think I understand what you want.  And yes, it is possible.  What you'll want to do is avoid early filtering of the quarters that you don't want shown.  Table calculations such as Window_Avg can only operate on the data that is returned from the source and is present in the data engine.

So, instead of filtering out the quarters using the Date field as a filter, create a calculated filter that uses a table calc and then use that as the filter.  That will apply the filter after the Window_Avg calculation has been evaluated.

The code will look something like:

LOOKUP([Date], 0)

The Lookup function is a table calculation and when used as a filter, it will effectively hide the data from the final rendering of the view, but it's still there for other table calculations.

Regards,

Joshua

• 5. Re: alternative to window average?

ok thank you, will sure give that a shot

• 6. Re: alternative to window average?

perhaps almost there ! thanks a bunch. you mentioned the lookup function, but i'm working with aggregate data, so that does not seem to work - so perhaps need to lookup distinct date or average date, or is that even feasible?

• 7. Re: alternative to window average?

Ah!  Yes, you are correct -- table calcs require aggregates.  Try using ATTR, like this:

LOOKUP(ATTR([Date]), 0)

Regards,

Joshua

1 of 1 people found this helpful
• 8. Re: alternative to window average?

thanks Joshua that did it. learned a lot. regards