'Lagging' Time-Scale in Discovery
Hi all,
Please see attached screenshot.
I am trying to build a section of an existing report at my company in discovery for export to excel (for the time being). Our report will have a time scale down the left (full year 2018 in this case), actual customers, budget customers and variance.
From there, we display a 2017 customer (currently just listed as 'Customer' on the far right) number and then a YoY variance (not currently shown) - my question is how can i lag a value by a year in relation to the member time scale displayed on the left? I've tried various combinations in 'formulate' only for these to overwrite the 2018 timescale already in there.
Any help would be greatly appreciated, thank you all.
James
1 reply
-
Hi James,
If I understood correctly, you need a measure formulation of your CUSTOMERS of the previous year.
Generally you could use the AddYears function, with -1 to get the previous year's date.
So for 2018-01-03 you would get 2017-01-03.
But it seems you have values only for Wednesdays, so in this case it won't work.
Instead you can use the AddWeeks function, with 52, to get the same Wednesday on the previous year:
1.
*Syntax = ([measures].[Sheet1 CUSTOMERS], addWeeks([Sheet1].[Week_End_Date],-52))
You could also use Lag 52, to get the same result:
*Syntax = ([measures].[Sheet1 CUSTOMERS],Lag([Sheet1].[Week_End_Date],52))
(AddWeeks takes the date and calculates 52 weeks back, Lag takes the element and finds the 52 element before it in your data - in this case all the elements are Wednesdays, so it's the same result).
2.
Then pick your dates for the rows (you can use a range list to get all the 2018 weeks)
so you will get this grid:
where 2017 looks like this, so you can see the numbers match.
3.
Then, to create the Year On Year calculation, you can use the quick menu in discovery.
Just select the 2 measures and create a ratio:
And that is it:
I hope this answers your question,
Imbar