expression for dynamic dimension

[#1]

HI

My user wants to select a certain month and see values from the last 6 months in relation to the selection.

Thought a dynamic dimension would be the solution but found out that I wasn't able to create an expression that would return me the needed list.

like:

selected month_ID = 1250

dynamic dimension for month_ID = 1244..1250

Juerg

[alexandros17]

if you have an ascending ID associated to every month it is enough to do ID>=1250-6 and ID<=1250

[#3]

Hi

do something like this

sum({<YearMonthNum={">$(=max(addmonths(YearMonthNum),-6)) <=$(=max(YearMonthNum))"}>} value)

best regards

Chris

[Nicole-Smith]

See the set analysis in the expression in the attached example.

[MK_QSL]

SUM({<Month_ID= {">=$(=Max(Month_ID)-5)<=$(=Max(Month_ID))"}>}SalesValue)

[Anonymous]

I noticed you used 'single quotes' instead of "double quotes".  Is there a particular reason or is there any scenarios where it would matter which one you used?

sum({<month_ID={'>=$(=max(month_ID)-6)<=$(=max(month_ID))'},Month=>}Sales)

     instead of

sum({<month_ID={">=$(=max(month_ID)-6)<=$(=max(month_ID))"},Month=>}Sales)

[Nicole-Smith]

You can use single quotes as long as you don't have single quotes within the single quotes.  For my original calculation, both single and double quotes can be used, but if we had something like the following, double quotes would need to be used on the outside since single quotes are used on the inside:

sum({<month_ID={">=$(=max({<ID={'001'}>}month_ID)-6)<=$(=max({<ID={'001'}>}month_ID))"},Month=>}Sales)

[#8]

Hi

wouw - many answers - great thanks

Nicole - can't open your example, have only a private license at home.

In general - all responses try to limit the expression result as I understand - and are not trying to create a reduced calculated dimension, meaning not all month-ID's but only a subrange of them?

I also tried to find an option with Dimension limits but could not find options to meet my requirements. For me this would have been the most obvious place to limit dimensional values.

The limited dimensional values in the Dimensions section would provide me with an easier to understand formula in the expressions section (set expressions are a pain to create and interprete [at least to me])?

Or did I get things wrong?

Juerg

[Nicole-Smith · Accepted solution]

Set analysis in the expression will limit what is given in the dimensions.

In my example, the dimension in my chart is the field Month, and my expression is:

sum({<month_ID={'>=$(=max(month_ID)-6)<=$(=max(month_ID))'},Month=>}Sales)

The set expression limits my dimensions as you can see in this screenshot (the raw data is on the left and the chart with the set expression is on the right):

EDIT: If you wanted to do it in the dimension without using set analysis, you could use this:

=aggr(if(month_ID >= $(=max(month_ID))-6 and month_ID <= $(=max(month_ID)),Month), Month)

However, I would recommend using the set analysis in the expression over this.  Performance with set analysis is better than using an if statement.