Date object creation based on Rowno()

[#1]

Hello,

Hope this problem is easy to solve. I've got a pivot table with months in first column,

january in row 1, Feb in row 2 etc. I want to do calculastion based on it.

I wrote some simple expression, but it return 0 in all columns:

=Sum(IF(Date([Hire / Rehire Date]) < Date('1/'&rowno()&'/2011'),1,0)) 

This on the other hand works fine

Sum(IF(Date([Hire / Rehire Date]) < Date('1/1/2011'),1,0))

When I tried checking date only it also works fine:

=Date('1/'&rowno()&'/2011')

What am I doing wrong in my 1st example?

BR,

Kuba

[#2]

Its very strange, but for whatever reason the thing that seems to be throwing this off is the Sum function on the final expression you are looking for.

When I remove the sum function it returns the right value.

I created a workaround, see if it works for you...

Go to settings->variable overview... and add a new variable called vDate.  Define it as:

=IF(Date([Hire / Rehire Date]) < Date('1/'&rowno()&'/2011'),1,0)

Now in your expression you can simply write = SUM(vDate).

[#3]

Hello,

Unfortunetely it is not working for me, "0" is shown in all columns.

[jagan]

Hi,

Check whether [Hire / Rehire Date] and

Date('1/'&rowno()&'/2011') are in the same format.  If not then convert both dates to the same format.


=IF(Date([Hire / Rehire Date]) < Date('1/'&rowno()&'/2011'),1,0)

Also you can also try like this

=IF(Date([Hire / Rehire Date]) < MakeDate(2011, rowno(), 1),1,0)

check both the dates are in the same format.

Hope this helps you.

Regards,

Jagan.

[#5]

Hello,

It must be some problem inside Qlikview...

Not working (0 shown in column):

=IF(Date([Hire / Rehire Date]) < MakeDate(2011, rowno(), 1),1,0)

Working (proper numbers shown):

=IF(Date([Hire / Rehire Date]) < MakeDate(2011, 1, 1),1,0)

On the other hand when I enter this as expression:

MakeDate(2011, rowno(), 1)

MakeDate(2011, 1, 1)

Both are working, and same date is shown... Really strange...

BR,

Kuba

[jagan]

Hi,

Try this,

=Sum(Aggr(IF(Date([Hire / Rehire Date]) < MakeDate(2011, rowno(), 1),1,0), [Hire / Rehire Date]))

Regards,

Jagan.

[#7]

Hi,

When I entered this as an expression nothing showed up (null).

BR,

Kuba

[jagan]

Hi,

Try this,

=Sum(Aggr(IF(Date([Hire / Rehire Date]) < MakeDate(2011, rowno(), 1),1,0), [Hire / Rehire Date], Month))

If it not works, can you attach the sample file.

Regards,

Jagan.

[#9]

Hi,

sorry what is Month in your example?

BR,

Kuba

[CELAMBARASAN]

Hi,

     jagan mohan suggested you to use the MonthFieldName in aggr function.

Celambarasan