Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

kishoreravi1983
New Contributor III

Calculated Dimension

Hi Friends,

I am stuck in a Calculation

I have Data for Last 4 Years.

But i would want to show only For latest 3 years and latest 12 Quarters and Latest 36 Months only.

I am able to derive this for years with Calculated Dimension. =If(Year>=$(vYears),Year)

vYears=MAX(Year,2)-1

But i am unable to create the same for Quarters and Months. If you see below image i have Quarters info from 2014 Q1 to 2017 Q3

I would like to show in my graph only 2015 Q1 to 2017 Q3 : Only Latest 12 Quarters Always.

Attached Sample Data file too.

Quarters.JPG

Capture.JPG

Please can you give me some pointers to it.

1 Solution

Accepted Solutions

Re: Calculated Dimension

I am assuming that YearQuarter field you have created in Master Calendar.

You can create Dual field for YearQuarter something like below.

Change according to your need. This is just an example.

Dual(Year & '-' & Quarter , AutoNumber(Year & '-' & Quarter,'YearQuarter'))  as YearQuarter

and use as below calculated dimension

=IF(YearQuarter >= (Max(Total YearQuarter)-11) and YearQuarter <= Max(Total YearQuarter), YearQuarter)

and ignore filter of YearQuarter in your expression

SUM({<YearQuarter>}YourExpression)

Also if you want last 12 quarters irrespective of Quarter or year selection then use as below

=IF(YearQuarter >= (Max({1}Total YearQuarter)-11) and YearQuarter <= Max({1}Total YearQuarter),YearQuarter)

7 Replies

Re: Calculated Dimension

I am assuming that YearQuarter field you have created in Master Calendar.

You can create Dual field for YearQuarter something like below.

Change according to your need. This is just an example.

Dual(Year & '-' & Quarter , AutoNumber(Year & '-' & Quarter,'YearQuarter'))  as YearQuarter

and use as below calculated dimension

=IF(YearQuarter >= (Max(Total YearQuarter)-11) and YearQuarter <= Max(Total YearQuarter), YearQuarter)

and ignore filter of YearQuarter in your expression

SUM({<YearQuarter>}YourExpression)

Also if you want last 12 quarters irrespective of Quarter or year selection then use as below

=IF(YearQuarter >= (Max({1}Total YearQuarter)-11) and YearQuarter <= Max({1}Total YearQuarter),YearQuarter)

kishoreravi1983
New Contributor III

Re: Calculated Dimension

Hi Manish,

Thanks for your Reply.

Apparently i dont have Master Calendar in my Script. All i have are only those three columns which are attached.

I am Not able to filter has TYQUARTER is coming has string value.

Regards,

Ravi

Re: Calculated Dimension

Data:

LOAD TYYEAR,

     TYQUARTER as TYQUARTERTEMP,

     TYMONTH,

     TY_NETDOLLARS

FROM

[..\Downloads\Sample Data File.xls]

(biff, embedded labels, table is Sheet1$);

Load Distinct TYQUARTERTEMP, Dual(TYQUARTERTEMP,AutoNumber(Left(TYQUARTERTEMP,4)&Num(Right(TYQUARTERTEMP,1),'00'),'TYQUARTER')) as TYQUARTER Resident Data

Order By TYQUARTERTEMP;

Use above script and follow all other steps from previous reply.

Re: Calculated Dimension

You can also use

TYQUARTER as dimension with below expression rather than Calculated Dimension

=SUM({<TYQUARTER = {'>=$(=Max(TYQUARTER)-11)<=$(=Max(TYQUARTER))'}>}TY_NETDOLLARS)

kishoreravi1983
New Contributor III

Re: Calculated Dimension

Bingo...Its working now..Thanks a Ton  Manish

Also i did the same for Months, but i am getting only 10 Months  instead of 12 Months...

Do i need to make any change in EXP:

Dual(TYMONTH,AutoNumber(Left(TYMONTHTEMP,6)&Num(Right(TYMONTHTEMP,2),'00'),'TYQUARTER')) as TYMONTHTEMP1

=IF(TYMONTHTEMP1 >= (Max(Total TYMONTHTEMP1)-12) and TYMONTHTEMP1 <= Max(Total TYMONTHTEMP1), TYMONTHTEMP1)

Regards,

Ravi

kishoreravi1983
New Contributor III

Re: Calculated Dimension

I am getting now. Once Again Thanks Manish

Re: Calculated Dimension

Please close the thread by selecting appropriate answer.

Community Browser