topic Re: Date Field is not picking correct value in QlikView

Date Field is not picking correct value

Thu, 06 Aug 2015 08:30:30 GMT

Hi,

I have

i want to calculate Duration =start date-end date with condition [Project Type]-IT

So i wrote expression for Duration= Max({<[Project Type] = {IT}>}Date([Exp End Date]))-Min({<[Project Type] = {IT>}Date([Start Date]))

But its not picking any value.

pls suggest.

sunny_talwar — Thu, 06 Aug 2015 08:34:21 GMT

Try this may be

=Max({<[Project Type] = {IT}>}Date#([Exp End Date], 'DD/MM/YYYY'))-Min({<[Project Type] = {IT>}Date#([Start Date], 'DD/MM/YYYY'))

=Max({<[Project Type] = {'IT'}>}Date#([Exp End Date], 'DD/MM/YYYY'))-Min({<[Project Type] = {'IT'}>}Date#([Start Date], 'DD/MM/YYYY'))

vardhancse — Thu, 06 Aug 2015 08:38:10 GMT

or else in script level also while loading we can write below script.

Date(Floor([Start Date]) as Start_Date,

Date(Floor([Exp End Date]) as End_Date

So that in entire dashboard we can use direct fields

Thu, 06 Aug 2015 08:39:26 GMT

Still it didnt pick up..

sasiparupudi1 — Thu, 06 Aug 2015 08:59:13 GMT

put this in a text box ans see if it returns the correct value

Max({<[Project Type] = {'IT'}>}Date#([Exp End Date], 'DD/MM/YYYY'))

MaxString({<[Project Type] = {IT}>}Date([Exp End Date]))

MarcoWedel — Thu, 06 Aug 2015 22:59:48 GMT

Typo:

Max({<[Project Type] = {IT}>}Date([Exp End Date]))-Min({<[Project Type] = {IT}>}Date([Start Date]))

hope this helps

regards

Marco

P.S.: no need for the Date() function as it is only formatting a numerical value, i.e.

=Max({$<[Project Type]={'IT'}>} [Exp End Date])-Min({$<[Project Type]={'IT'}>} [Start Date])

will also work.

MayilVahanan — Fri, 07 Aug 2015 05:27:37 GMT

For Difference, you can use like this

=Max({<[Project Type]={'IT'}>} [Exp End Date])-Min({<[Project Type]={'IT'}>} [Start Date])

No need date function in your case.