topic Re: date function problem in QlikView

date function problem

srikantj — Wed, 25 Nov 2020 16:16:04 GMT

if([Req2 Date]<[Run2 Date], MonthStart([Run2 Date],0)

why it is giving blank spaces when i use it as straight table expression

Qlikss Sugessttttt

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 13:38:09 GMT

Are there more than one Req2 Date or Run2 Date for each dimension value? If that's the case, you will need to use some sort of Aggregation to know which of the multiple dates to use (like Min or Max)

Re: date function problem

srikantj — Thu, 02 Mar 2017 13:41:53 GMT

hi sunny

req2 date run2 date ---- dimensions

if([Req2 Date]<[Run2 Date], MonthStart([Run2 Date],0) -- Expression

What i have to do

Re: date function problem

srikantj — Thu, 02 Mar 2017 13:43:18 GMT

and i have 6000 rows for each date field

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 13:45:19 GMT

If those are dimension, then we don't have to worry about aggregations. Next thing to check is to see if Req2 Date and Run2 Date are read as date by qlikview or not. If they are strings, then the if statement will not work and so will Monthstart

Why don’t my dates work?

QlikView Date fields‌

Get the Dates Right

Re: date function problem

srikantj — Thu, 02 Mar 2017 13:48:31 GMT

if([Req2 Date]<[Run2 Date],1,0)

it is working fine for this expression

but when i add monthstart fucnction it is not working

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 13:49:26 GMT

Can you share a screenshot of the two expressions

1) if([Req2 Date]<[Run2 Date], MonthStart([Run2 Date],0)

2) if([Req2 Date]<[Run2 Date],1,0)

Re: date function problem

srikantj — Thu, 02 Mar 2017 13:53:57 GMT

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 13:56:33 GMT

Oh you have a missing parenthesis, try this

if([Req2 Date]<[Run2 Date], MonthStart([Run2 Date]),0)

Re: date function problem

srikantj — Thu, 02 Mar 2017 13:59:28 GMT

But it is coming in number format .. why it is not coming date format

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 14:01:39 GMT

Try this:

MonthStart(If([Req2 Date]<[Run2 Date], [Run2 Date],0))

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 14:02:19 GMT

Or this:

Date(If([Req2 Date]<[Run2 Date], MonthStart([Run2 Date]),0)

Or you can set the format on the numbers tab to show as Date

Re: date function problem

srikantj — Thu, 02 Mar 2017 14:02:53 GMT

thanks alot sunny . i used date function to convert num into date

it is working fine

Re: date function problem

srikantj — Thu, 02 Mar 2017 14:03:57 GMT

Thanks alot sunny

Re: date function problem

sunny_talwar — Thu, 02 Mar 2017 14:04:03 GMT

Great, please close the thread by marking correct and helpful responses.

Best,

Sunny