topic Re: Comparing two dates in Set Analysis challenge in App Development

Comparing two dates in Set Analysis challenge

deerhunter — Wed, 11 Jan 2017 17:01:41 GMT

What is wrong with this set analysis statement?

sum({<Scenario = {'Actual'}, Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY))) = {'=$(vCurrentDate}' >} AMOUNT)

In the load script I have:

LET vCurrentDate = Today();

Which generates:

1/11/2017

Testing the timestamp removal works as expected:

Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY)))

changes datetime of --> 5/4/2016 12:00:00 AM --> to 5/4/2016 as expected

In a table object I have this that is not working:

sum({<Scenario = {'Actual'}, Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY))) = {'=$(vCurrentDate}' >} AMOUNT)

It returns all of the rows when I should only return the one THAT MATCHES THE DATE!

Can anyone see the issue?

Thanks in advance....

Re: Comparing two dates in Set Analysis challenge

sunny_talwar — Wed, 11 Jan 2017 17:14:16 GMT

Do this in the script:

Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY))) as PR_BUSINESS_DAY

and then this:

Sum({<Scenario = {'Actual'}, PR_BUSINESS_DAY = {'$(=vCurrentDate)'}>} AMOUNT)

Or try this:

Sum({<Scenario = {'Actual'}, PR_BUSINESS_DAY = {"=Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY))) = $(vCurrentDate)"}>} AMOUNT)

Re: Comparing two dates in Set Analysis challenge

deerhunter — Wed, 11 Jan 2017 20:30:26 GMT

Awesome, the first suggestion worked but timed out. The second returned a single row really quick with nothing in it

Re: Comparing two dates in Set Analysis challenge

petter — Wed, 11 Jan 2017 21:00:47 GMT

You can only have a bare field on the left-hand-side of the = inside the modifiers which are < and >.

Sum({<Scenario = {'Actual'}, PR_BUSINESS_DAY_DT={'$(vCurrentDate'}>} AMOUNT)

You need to create an extra field by doing this in the appropriate LOAD:

LOAD

....

Date(FLOOR(TimeStamp#(PR_BUSINESS_DAY))) AS PR_BUSINESS_DAY_DT

....

Re: Comparing two dates in Set Analysis challenge

sunny_talwar — Thu, 12 Jan 2017 12:30:15 GMT

So did you manage to get this resolved? I am a little confused from your response. You wrote 'Awesome' which made me think that something worked..... but then first expression timed out? it showed a result and then timed out? (and second one didn't really work at all)

So I wonder if its resolved or not

Re: Comparing two dates in Set Analysis challenge

deerhunter — Fri, 13 Jan 2017 14:48:25 GMT

Yes it worked, very sorry for the confusion. i used your first example by placing it in the load.

Thank you sooooooo much

Re: Comparing two dates in Set Analysis challenge

sunny_talwar — Fri, 13 Jan 2017 14:51:34 GMT

Great, please close the thread by marking the correct response also.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Comparing two dates in Set Analysis challenge

deerhunter — Fri, 13 Jan 2017 15:39:34 GMT

One more thing, how do i do that? Close the thread!

Re: Comparing two dates in Set Analysis challenge

sunny_talwar — Fri, 13 Jan 2017 15:44:40 GMT

Shared a link above which tells you exactly how to close a thread (Sharing it again)

Qlik Community Tip: Marking Replies as Correct or Helpful