I believe you can just remove the '-4,0' arguments from the calculated field, and use:
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.
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.
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:
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.
ok thank you, will sure give that a shot
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?
1 of 1 people found this helpful
Ah! Yes, you are correct -- table calcs require aggregates. Try using ATTR, like this:
thanks Joshua that did it. learned a lot. regards