Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kevincase
Creator II
Creator II

Dates in Set Analysys.

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?

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

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

View solution in original post

7 Replies
Anonymous
Not applicable

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?

Not applicable

Could you post your sample data?

kevincase
Creator II
Creator II
Author

Yes, they display as the same format. 

kevincase
Creator II
Creator II
Author

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.

Not applicable

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
Partner - Master III
Partner - Master III

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
Creator II
Creator II
Author

Liron,

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