topic Calculated dim in QlikView

Calculated dim

Mahamed_Qlik — Tue, 29 Mar 2016 12:28:58 GMT

Dear all,

I have scenario as -

Dimension names as year

Year
2011
2012
2013
2014

But it is not static, it will increase in 2015.

I need calculated dimension as only last two year like -

New_Year
2013
2014

How do i will do that?

Kindly advise.

Regards,

mahamed

Re: Calculated dim

swuehl — Tue, 29 Mar 2016 12:32:12 GMT

You can either use a calculated dimensions

=Aggr( If(Year >= Max(TOTAL Year)-1,Year), Year)

or use a set expression in all your expressions, like

=Sum({<Year = {">=$(=Max(Year)-1)"}>} Value)

edit:

Your calculated dim could also just like

=If(Year >= $(=Max(Year))-1, Year)

of course.

Re: Calculated dim

sunny_talwar — Tue, 29 Mar 2016 12:33:46 GMT

May be this as your calculated dimension:

If(Match(Year, Max(TOTAL Year), Max(TOTAL Year) - 1), Year)

Re: Calculated dim

Clever_Anjos — Tue, 29 Mar 2016 12:35:35 GMT

Please try

=Aggr(Only({<Year={">=$(=Max(Year,2))"}>}Year),Year)

Re: Calculated dim

Tue, 29 Mar 2016 12:39:23 GMT

Hi Mahamed Khan, you can do this:

In 'Calculated dimension' put this expresion:

if(YEAR='2013' or YEAR='2014', YEAR)

if( year(today())-1= YEAR or year(today())-2= YEAR , YEAR) // with this example you always will see the last two years

Regards,

Agustín

Re: Calculated dim

Tue, 29 Mar 2016 12:39:43 GMT

Hi Mahamed Khan,

To get the last two years in the data you can use the dimension as :

=if(Year >= Max(Total Year)-1,Year)

Regards,

Anjali Gupta

Re: Calculated dim

dusasuman — Tue, 29 Mar 2016 12:51:22 GMT

Hi,

Try this code in calculated dimension.

=if(aggr(rank(Year)<=2,Year),Year)

It is working.. Make sure you do check Suppress null values on dimension tab.

Regards

Suman

Re: Calculated dim

dusasuman — Tue, 29 Mar 2016 12:57:26 GMT

Hi,

Try this code in calculated dimension.

=if(aggr(rank(Year)<=2,Year),Year)

It is working.. Make sure you do check Suppress null values on dimension tab.

Regards

Suman