topic Re: Calculated Dimension - expression in QlikView

Calculated Dimension - expression

Mon, 02 Dec 2013 10:36:00 GMT

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

tresB — Mon, 02 Dec 2013 10:38:20 GMT

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: Calculated Dimension - expression

jagan — Mon, 02 Dec 2013 10:39:19 GMT

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

Mon, 02 Dec 2013 10:45:45 GMT

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: Re: Calculated Dimension - expression

Mon, 02 Dec 2013 10:48:00 GMT

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

tresB — Mon, 02 Dec 2013 10:50:03 GMT

Sorry, need to correct. add TOTAL keyword like:

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

Re: Calculated Dimension - expression

udit_kumar_sana — Mon, 02 Dec 2013 10:51:08 GMT

Hi ,

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

Thanks & Regards,
Udit

Re: Calculated Dimension - expression

jagan — Mon, 02 Dec 2013 10:51:12 GMT

Hi,

Can you attach some sample file.

Regards,

Jagan.

Re: Re: Calculated Dimension - expression

Mon, 02 Dec 2013 10:54:55 GMT

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!