Metrics as Dimension

[Anonymous]

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

[YoussefBelloum]

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 ?

[OmarBenSalem]

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.

[parthesh]

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

[Anonymous]

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..