Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anupamajagan
Creator
Creator

Metrics as Dimension

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

4 Replies
YoussefBelloum
Champion
Champion

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

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

anupamajagan
Creator
Creator
Author

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