topic Dates in Set Analysys. in QlikView

Dates in Set Analysys.

kevincase — Mon, 29 Sep 2014 16:29:49 GMT

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?

Re: Dates in Set Analysys.

Anonymous — Mon, 29 Sep 2014 16:40:09 GMT

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?

Re: Dates in Set Analysys.

Mon, 29 Sep 2014 16:47:38 GMT

Could you post your sample data?

Re: Dates in Set Analysys.

kevincase — Mon, 29 Sep 2014 17:11:27 GMT

Yes, they display as the same format.

Re: Re: Dates in Set Analysys.

kevincase — Mon, 29 Sep 2014 17:35:06 GMT

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.

Re: Re: Dates in Set Analysys.

Mon, 29 Sep 2014 18:29:54 GMT

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

Re: Re: Re: Dates in Set Analysys.

lironbaram — Mon, 29 Sep 2014 18:35:11 GMT

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

Re: Re: Re: Dates in Set Analysys.

kevincase — Tue, 30 Sep 2014 14:13:17 GMT

Liron,

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