Dates in Set Analysys.

[kevincase]

I have a fairly simple expression that does a SUM based on some set analysis.  It works perfectly until I try to compare two dates.  My expression is:  Sum({<{who={1,6}, [Stip Deactivated]={'No'}, [Stip Cleared]={'No'}, [Stip Evaluated={'Yes'}>} [Stip Count])

All dates loaded into the model are loaded as follows:

Date(Floor("Stip Evaluated Date")) as [Stip Evaluated Date],

Date(Floor("Stip Submitted Date")) as [Stip Submitted Date],

What I need to do is add Stip Submitted Date < Stip Evaluated Date. I have tried countless ways to do this but it never seems to evaluate.  I have tried [Stip Submitted Date]={"<$(=Date([Stip Evaluated Date]))"}.  I gave tried this with both single and double quotes.  Nothing seems to evaluate correctly.  Any suggestions?

[Anonymous]

the set Expression Looks good!

can you test in a TextBox if [Stip Submitted Date] and Date([Stip Evaluated Date]) have the same date formats?

[#3]

Could you post your sample data?

[kevincase]

Yes, they display as the same format. 

[kevincase]

http://Here is a quick sample.  I want to add the condition that Stip Submitted Date < Stip Evaluated Date.  This should drop the sum to around 1293 (give or take).  This is old sample data.

[#6]

Hi Try something like below:

=Sum({<Who={1,6}, [Stip Deactivated]={'No'}, [Stip Cleared]={'No'}, [Stip Evaluated]={'Yes'}, [Stip Submitted Date] = {"<$(=max([Stip Evaluated Date]))"}>} [Stip Count])

hope it helps

[lironbaram · Accepted solution]

hi

once again i think there is a little bit confusion on what set analysis those

set analysis is like making a selection in the model , so basically it returns the same result to all rows in a table or expression

so i your case you need to use if statement

i changed the expression to

Sum({<Who={1,6}, [Stip Deactivated]={'No'}, [Stip Cleared]={'No'}, [Stip Evaluated]={'Yes'}>} if([Stip Submitted Date]<[Stip Evaluated Date], [Stip Count]))

and it seems to work perfectly

[kevincase]

Liron,

This seems to give me what I need.  Thanks for the help.