topic Filtering future months in QlikView

Filtering future months

Fri, 20 Jul 2012 07:58:19 GMT

Hi, I'm relatively new at qlikview and I've been trying to filter future months to see if there's information saved in incorrect months (ex: It's impossible to spend money in October 2012 if we're in July 2012).

I'd like to count the number of transactions made in future months of the same year. This is the formula I've been trying to use:

=Count({<transaction_Year={"=year(Today())"},transaction_Month={">month(Today())"}>}Transaction)

It works fine with the year, but when I try to filter greater months (transaction_Month has a numerical value) it just doesn't work. I don't get an error in the expression, but the numbers are all to zero.

I would greatly appreciate any help.

Re: Filtering future months

Fri, 20 Jul 2012 10:15:09 GMT

try to use :

=num(Month(TODAY()))

or change

SET MonthNames='sty;lut;mar;kwi;maj;cze;lip;sie;wrz;paź;lis;gru';

in your loading script

Re: Filtering future months

Fri, 20 Jul 2012 10:28:59 GMT

Thanks that helped. I had to use a variable (=num(Month(TODAY()))) and it worked well.

I had already resolved the issue by creating the following variable: =If(Month(Today())='jan', 1, if(Month(Today())='feb', 2, if(Month(Today())='mar', 3, if(Month(Today())='abr', 4, if(Month(Today())='may', 5, if(Month(Today())='jun', 6, if(Month(Today())='jul', 7, if(Month(Today())='ago', 8, if(Month(Today())='sep', 9, if(Month(Today())='oct', 10, if(Month(Today())='nov', 11, if(Month(Today())='dec', 12, 0)))))))))))).

As you can see your method is much cleaner. Thanks for your help.

Re: Filtering future months

Fri, 20 Jul 2012 10:33:30 GMT

it will be better if you use one field DATA instead of YEAR AND MONTH fields

then it'l be easy to filter mar 2013