Date Field is not picking correct value

[#1]

Hi,

I have

[Start Date]	[Exp End Date]
23/02/2015	31/07/2015
07/05/2015	13/05/2015
13/03/2015	15/04/2015
07/07/2015	15/07/2015
01/06/2015	29/06/2015
01/06/2015	22/06/2015
01/07/2015	15/07/2015

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]

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]

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

[#4]

Still it didnt pick up..

[sasiparupudi1]

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

or

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

[MarcoWedel]

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]

Hi

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.