Creating a Moving Annual Average
Hi,
I am trying to create a moving annual average of customers to feature on a graph I have i created in discover (please see attached).
How would I go about doing this? So the first point on the graph (the first week in 2018) would be an average of customer numbers from the first week of 2017 and would maintain a rolling 52 week average as it progressed through the graph.
Any help would be greatly appreciated as I have exhausted my (albeit limited) knowledge of the system and how to do this!
Thank you in advance,
James
4 replies
-
Hi James,
I was just recently tackling this challenge and managed to solve it... :)
First, you have to select Avg function out of Numeric list (Pic 1). Then you select "Range List" as a set and "Data Point" from Element Blocks. In Range list, you pick first and last members using functional selections (Pic 2). CurrentMember for the last period and Lag for the first period (number of periods backwards). In Data Point, you select the measure by which you want to calculate.
That is it, hope it Works for you too. :)
Uroš
-
Sorry, forgot to include images...
-
This is great thanks. I've been working on something similar for the average 8(X) weeks. I was about go to and do it cubeside instead, which would remove the parameter.
Thanks Uroš Zdešar -
Good stuff guys!
Please be aware though, if you are reading this thread, that this only applies when using SSAS OLAP or Tabular cubes with MDX.
This approach using PQL for our In Memory DB or SQL DBs is not currently supported, you will get an error if you use a relative member reference (current Member, LAG(), etc.) for the range endpoints in the Range Set, even though the UI lets you pick them.
This type of PQL functionality is planned for a future release.
Cheers.
Ian
