topic Year2Date calculations issue in QlikView

Year2Date calculations issue

Wed, 21 Oct 2009 19:43:03 GMT

Hi all,

I have a question about calculations.

I have to variables:
MaxSelecPeriod --> Contains the highest selected date number (number field, not date field) Ex: 200910
MaxSelecYear --> Contains the year number of the selected date (number field, not date field) EX: 2009

Now I want to write an expression in my Straight table column that will give me the SUM([Actual Fact]) for the each [YearMonth] (number field, not date field) that is equal to or lower than ( <= ) than the MaxSelecPeriod, and for which the [Year] (number field, not date field) equals MaxSelecYear.

Should/can I do this with IF statements, should I use the Set Analysis, should I do this in some other way?

I think it is best to use a Set Analysis statement because witht he IF statement I tried I got no result at all when I have no MonthYear selected yet.
Also; what is the expression for 'lower or equal to'?

Thanks already,
With kind regards

Year2Date calculations issue

Wed, 21 Oct 2009 20:14:51 GMT

Hi Tim,

maybe this is what you are looking for:


Sum (
 {1<
 MaxSelecYear={$(=Max(MaxSelecYear))},
 MaxSelecPeriod={'<=$(=MaxSelecPeriod'}
 >}
 [Actual Fact]
)

Rgds,

Rey-man

Year2Date calculations issue

Wed, 21 Oct 2009 20:45:54 GMT

Hi Rey-man,

Thanks for your answer.

Not quite there yet, though.
The MaxSelecYear and MaxSelecPeriod are variables, not fields. As such, each variable has only one number in it for a certain selection. Comparing it with the max of this number seems a bit strange.

Also, do you know how to properly introduce a Variable into a set expression?

Thanks again,

Tim

Year2Date calculations issue

Thu, 22 Oct 2009 14:23:12 GMT

Hi Tim,

you can use something like this:

Year={"<=$(MaxSelecYear )"}

Hope this helps you

Rey-man