How does on a dimension work "activate the condition"

[Anonymous]

Is there a way to use "activate the condition" on a dimension to limit my dimension.

In this case my dimension is a month and I want to limit to 12 months.

What's the use of this "activate the condition" ?

I tried other things order and dimensional limit, but it's never what I want (the 12 last months from the 1st to the last).

[qlik4asif]

Create Calculated condition

If(Num(Month)<=12,Month)

[stigchel]

The "activate the condition" is for a conditional Dimension, to use it or not. So based on a condition you can use this dimension or e.g. another.

The dimensional limit (I agree annoyingly) can only use the first expression in the expression tab. For a straight table you could use

=Num(Month)

as your first expression and hide it in the presentation tab. Now you can use the Dimension limit to show the 12 largest months (assuming your month is a dual with e.g. Monthstart() as date, otehrwise use Max(Date) or something)

[stigchel]

Otherwise you can use set analysis in the expression to limit the dimension, something like (search the forum for other examples)

sum({< Date={'>=$(=AddMonths(Today(),-12)'}>} FieldToSum)

[Peter_Cammaert]

Keep in mind that there are no more than 12 months in an average calendar. If you only filter on month or monthnumber, the condition will become useless because all monthnumbers are <= 12 anyway.

It would be better if you have a field with a first-of-the-month value or a MonthYear value. Suppose you have an Orders table with an OrderDate field that can contain any date. The following creates an additional field but this time containing the first of the month of the OrderDate:

LOAD ...

     OrderDate,

     Date(MonthStart(OrderDate), 'MMM-YYYY') AS OrderDateMonthStart,

     ...

FROM Orders.QVD (...)

If you now add a calulated dimension like this to your chart, you will get only those rows that happen to fall in the 12 preceding months (the current month and the 11 months before):

IF (OrderDateMonthStart >= AddMonths(MonthStart(Today()), -11), OrderDateMonthStart)

You can get better performance in charts and tables by embedding this condition in a Set Analysis expression like the following:

=Sum({<OrderDateMonthStart={">=$(=Date(AddMonths(MonthStart(Today()), -11), 'MMM-YYYY'))"}>} OrderValue)

Best,

Peter