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: 
deerhunter
Creator
Creator

Comparing two dates in Set Analysis challenge

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

8 Replies
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
Creator
Creator
Author

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

petter
Partner - Champion III
Partner - Champion III

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

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

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