Calculated Dimension - expression

[#1]

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

[tresesco]

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.

[jagan]

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.

[#4]

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

[#5]

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

[tresesco · Accepted solution]

Sorry, need to correct. add TOTAL keyword like:

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

[udit_kumar_sana]

Hi ,

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

Thanks & Regards,
Udit

[jagan]

Hi,

Can you attach some sample file.

Regards,

Jagan.

[#9]

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!