Reload time in set analysis

[ilyas_coban]

Hi Everyone,

I am trying to write a set analysis to sum sales that sold in last 3 days. Nuance is I need to go back 3 days from reload time and I need to calculate this with set analysis because of performance issues. My problem is that if I write set analysis with today() it works but it is not what I want and if I replace today() with reloadtime() or now(0), it doesn't work. Time format is like this: "30.03.2016 10:57:29 AM" (quote marks are not included ). Here is the working code:

sum(  {< [TIME] = {">$(=timestamp(today()-3))"} >} Sales )

And this is not working:

sum(  {< [TIME] = {">$(=timestamp(reloadtime()-3))"} >} Sales )

How can I solve this problem?

Regards,

[ilyas_coban]

I do not create TIME. I am loading it directly from ERP DB.

[sunny_talwar]

I guess change the formatting of TIME in the script in that case:

LOAD TimeStamp(TIME) as TIME

and then try this:

Sum({<[TIME] = {">$(=TimeStamp(ReloadTime() - 3))"} >} Sales)

[ilyas_coban · Accepted solution]

I solved the problem. When I was hovering my code, I realized that reloadtime() and now(0) is creating a time stamp with 2 spaces between date and time instead of one but today() is creating a timestamp with one space between date and time. Then I changed my timestamp function into this: TimeStamp(reloadtime()-3, 'DD.MM.YYYY h:mm:ss TT') and it worked. I guess it is a bug, right?

Thanks for everyone helped.

[swuehl]

Could you post the expression that worked when you were replacing the dollar sign expansion with the constant string value retrieved from the popup? (Best, repost also the original expression).

Not sure if we have asked this before: Any chance to upload a small sample QVW?

[sunny_talwar]

Check you environmental variable. May be there are two spaces between date and time. Remove the space and reload and it should work.

[swuehl]

Well, Today() used as is doesn't return a time part at all, does it?

You probably used Timestamp() around Today() like you posted above

sum(  {< [TIME] = {">$(=timestamp(today()-3))"} >} Sales )

Since you used the same formatting for the reload(), I assume your default format just hasn't matched the TIME field format (like you said, difference between h:m:s and hh:mm:ss etc.).

Good that it's working now.

Please consider closing this thread by marking helpful or correct answer.

Qlik Community Tip: Marking Replies as Correct or Helpful

Regards,

Stefan