Comparing two dates in Set Analysis challenge

[deerhunter]

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....

[sunny_talwar]

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)

[deerhunter]

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

[petter]

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

     ....

[sunny_talwar]

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

[deerhunter]

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

Thank you sooooooo much

[sunny_talwar]

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[deerhunter]

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

[sunny_talwar]

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