Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

deerhunter
Contributor

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

Re: Comparing two dates in Set Analysis challenge

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
Contributor

Re: Comparing two dates in Set Analysis challenge

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

MVP
MVP

Re: Comparing two dates in Set Analysis challenge

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

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
Contributor

Re: Comparing two dates in Set Analysis challenge

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

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

deerhunter
Contributor

Re: Comparing two dates in Set Analysis challenge

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

Re: Comparing two dates in Set Analysis challenge

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

Community Browser