topic Metrics as Dimension in QlikView

Metrics as Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Metrics as Dimension

YoussefBelloum — Mon, 05 Mar 2018 10:33:14 GMT

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 ?

Re: Metrics as Dimension

OmarBenSalem — Mon, 05 Mar 2018 10:41:53 GMT

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.

Re: Metrics as Dimension

parthesh — Mon, 05 Mar 2018 10:46:19 GMT

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

Re: Metrics as Dimension

Anonymous — Mon, 05 Mar 2018 10:47:15 GMT

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