topic Re: Date object creation based on Rowno() in QlikView

Date object creation based on Rowno()

Thu, 01 Mar 2012 13:22:22 GMT

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

Thu, 01 Mar 2012 17:27:41 GMT

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

Fri, 02 Mar 2012 06:35:09 GMT

Hello,

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

Date object creation based on Rowno()

jagan — Fri, 02 Mar 2012 06:41:46 GMT

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

Fri, 02 Mar 2012 07:49:14 GMT

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

jagan — Fri, 02 Mar 2012 07:55:30 GMT

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

Fri, 02 Mar 2012 08:02:06 GMT

Hi,

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

BR,

Kuba

Date object creation based on Rowno()

jagan — Fri, 02 Mar 2012 08:10:51 GMT

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

Fri, 02 Mar 2012 09:02:26 GMT

Hi,

sorry what is Month in your example?

BR,

Kuba

Date object creation based on Rowno()

CELAMBARASAN — Fri, 02 Mar 2012 09:10:24 GMT

Hi,

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

Celambarasan

Date object creation based on Rowno()

jagan — Fri, 02 Mar 2012 09:21:17 GMT

Hi,

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

Regards,

Jagan.