topic Re: Calculated Dimension in QlikView

Calculated Dimension

kishoreravi1983 — Wed, 07 Jun 2017 08:43:19 GMT

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.

Please can you give me some pointers to it.

Re: Calculated Dimension

MK_QSL — Wed, 07 Jun 2017 08:56:43 GMT

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)

Re: Calculated Dimension

kishoreravi1983 — Wed, 07 Jun 2017 10:06:56 GMT

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

MK_QSL — Wed, 07 Jun 2017 10:19:09 GMT

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

MK_QSL — Wed, 07 Jun 2017 10:33:01 GMT

You can also use

TYQUARTER as dimension with below expression rather than Calculated Dimension

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

Re: Calculated Dimension

kishoreravi1983 — Wed, 07 Jun 2017 10:43:10 GMT

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

Re: Calculated Dimension

kishoreravi1983 — Wed, 07 Jun 2017 10:53:05 GMT

I am getting now. Once Again Thanks Manish

Re: Calculated Dimension

MK_QSL — Wed, 07 Jun 2017 11:01:29 GMT

Please close the thread by selecting appropriate answer.