Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following metrics - Subs, Bound, Quotes. I need to have a table which needs this metrics to be viewed as Current year, prior year, 3MM CY, 3 MM PY, 6 MM CY, 6 MM PY. The table structure should be like this:
Current year Prior year 3 MMCY 3 MMPY 6 MMCY 6 MMPY
Subs
Binds
Quotes
The 3 MM and 6 MM for both CY and PY should be rolling one. i.e. if we are in Jan , 3 MMCY should be Jan 2018, Dec 2017, Nov 2017
Similarly for 3 MM PY it should be Jan 2017, Dec 2016, Nov 2016.
Kindly let me know how to achieve this.
Thanks in Advance
Anupama Jagan
Hi,
your table is already structures ? or do you want to know how to put your columns to be able to analyze the way described above ?
create a Pivot table:
as a dimension; your dimension (containing Subs, Bound, Quotes)
as a column: create this:
valueList('Current year','prior year','3MM CY','3 MM PY','6 MM CY','6 MM PY')
then as a measure:
if(valueList('Current year','prior year','3MM CY','3 MM PY','6 MM CY','6 MM PY')='Current year',CurrentyearExpression,
and so on...)))))
This what you should do to have the desired structure output.
Now, if want an assistance on how to create these expressions, let us know.
For 3MM CY, 6MM CY, 3MM PY and 6MM PY
you can use below set analysis.
Sum({<Common_Date = {'>=$(=addmonths(YearStart(today()),-3)) <=$(=max(today()))'}>} amount)
Sum({<Common_Date = {'>=$(=addmonths(YearStart(today()),-6)) <=$(=max(today()))'}>} amount)
Sum({<Common_Date = {'>=$(=addmonths(YearStart(today()),-15)) <=$(=addmonths(YearStart(today()),-12))'}>} amount)
Sum({<Common_Date = {'>=$(=addmonths(YearStart(today()),-18)) <=$(=addmonths(YearStart(today()),-12))'}>} amount)
regards
So the valuelist I'm creating should be a calculated dimension right?
It would be great if you could help with atleast current year, prior year and 3 MM CY and 3 MM PY expressions..