topic Re: expression for dynamic dimension in QlikView

expression for dynamic dimension

Fri, 24 Jan 2014 15:43:05 GMT

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

Re: expression for dynamic dimension

alexandros17 — Fri, 24 Jan 2014 15:45:54 GMT

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

Re: expression for dynamic dimension

Fri, 24 Jan 2014 15:46:57 GMT

do something like this

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

best regards

Chris

Re: expression for dynamic dimension

Nicole-Smith — Fri, 24 Jan 2014 15:54:57 GMT

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

Re: expression for dynamic dimension

MK_QSL — Fri, 24 Jan 2014 15:57:44 GMT

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

Re: Re: expression for dynamic dimension

Anonymous — Fri, 24 Jan 2014 16:07:23 GMT

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)

Re: Re: Re: expression for dynamic dimension

Nicole-Smith — Fri, 24 Jan 2014 16:27:28 GMT

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)

Re: expression for dynamic dimension

Fri, 24 Jan 2014 20:09:53 GMT

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

Re: expression for dynamic dimension

Nicole-Smith — Fri, 24 Jan 2014 20:13:20 GMT

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.