10 Replies Latest reply: Mar 2, 2012 4:21 AM by jagan mohan rao appala

# Date object creation based on Rowno()

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

• ###### Date object creation based on Rowno()

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).

• ###### Date object creation based on Rowno()

Hello,

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

• ###### Date object creation based on Rowno()

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.

• ###### Date object creation based on Rowno()

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

• ###### Re: Date object creation based on Rowno()

Hi,

Try this,

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

Regards,

Jagan.

• ###### Re: Date object creation based on Rowno()

Hi,

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

BR,

Kuba

• ###### Date object creation based on Rowno()

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.

• ###### Date object creation based on Rowno()

Hi,

sorry what is Month in your example?

BR,

Kuba

• ###### Date object creation based on Rowno()

Hi,

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

Celambarasan

• ###### Date object creation based on Rowno()

Hi,

"Month" is your Month field name in you file.

Regards,

Jagan.