Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kishoreravi1983
Contributor III
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
MK_QSL
MVP
MVP

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)

View solution in original post

7 Replies
MK_QSL
MVP
MVP

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
Contributor III
Contributor III
Author

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

MK_QSL
MVP
MVP

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.

MK_QSL
MVP
MVP

You can also use

TYQUARTER as dimension with below expression rather than Calculated Dimension

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

kishoreravi1983
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

I am getting now. Once Again Thanks Manish

MK_QSL
MVP
MVP

Please close the thread by selecting appropriate answer.