8 Replies Latest reply: Dec 2, 2013 5:54 AM by Chanel Choo

# Calculated Dimension - expression

Hi community,

I want to display my chart from first week until last 6th weeks, I cannot limit it to show first xx weeks, because the week will keep increasing in the future, so i decided to write expression for dimension as shown below, but it is not working.

Appreciate if you could help.

Calculated Dimension:

=if(WeekDim < (max(WeekDim) - 5), WeekDim)

Thanks and best regards,

Chanel

• ###### Re: Calculated Dimension - expression

Use it with aggr() like:

=Aggr(if(WeekDim < (max(WeekDim) - 5), WeekDim), WeekDim)

By the way, I guess you can use dimension limit here. If your weeks increase the rank is calculated accordingly. I am not sure if I am missing anything of your requirement statement.

• ###### Re: Re: Calculated Dimension - expression

Thanks, I tried the expression but it is not working as well.

For dimension limits, i can only choose first, largest, smallest.

For my case, if there is 48 weeks, i just want to choose week1- 42, then in the following week, it will add on to 49 weeks, but i should not change this every week.

Best regards,

Chanel

• ###### Re: Calculated Dimension - expression

Sorry, need to correct. add TOTAL keyword like:

=Aggr(if(WeekDim < (max( TOTAL WeekDim) - 5), WeekDim), WeekDim)

• ###### Re: Calculated Dimension - expression

Hi,

Instead of going for Calculated Dimension, you can restrict the weeks in expression also, check below expression

=Sum({<MonthDimensionName=, WeekDimensionName=, YearDimensionName=, QuarterDimensionName=, DateDimensionName = {'>=\$(=YearStart(Max(DateDimensionName)))<=\$(=WeekEnd(Max(DateDimensionName)))'}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

• ###### Re: Re: Calculated Dimension - expression

I tried but it is a variable, so it is not working properly.

=num({<Date={"<=\$(=Date(max(Date)-35,'DD/MM/YYYY'))>=\$(=Date(min(Date)+42,'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>} \$(vROIaverage))

Thanks and best regards,

Chanel

• ###### Re: Calculated Dimension - expression

Hi,

Can you attach some sample file.

Regards,

Jagan.

• ###### Re: Re: Calculated Dimension - expression

Thanks Jagan for trying to help, we got answer from tresesco.

with calculated dimension:

=Aggr(if(WeekDim < (max( TOTAL WeekDim) - 5), WeekDim), WeekDim)

Really appreciate both of your helps!

• ###### Re: Calculated Dimension - expression

Hi ,

You can also try out from Presentation tab, and entering value in maximum number to be displayed.

Thanks & Regards,
Udit