topic Reload time in set analysis in QlikView

Reload time in set analysis

ilyas_coban — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Reload time in set analysis

dmac1971 — Fri, 01 Apr 2016 15:25:32 GMT

Today contains just the date, ie 01/04/2016. Reloadtime however contains date and time stamp, ie 01/04/2016 16:23:05 etc. Supect stripping out the time element might work?

Re: Reload time in set analysis

sunny_talwar — Fri, 01 Apr 2016 22:42:31 GMT

Can you try this:

Sum({<[TIME] = {">$(=TimeStamp(ReloadTime()-3, 'DD.MM.YYYY hh:mm:ss TT'))"} >} Sales)

Re: Reload time in set analysis

sunny_talwar — Fri, 01 Apr 2016 23:08:09 GMT

Based on how your environmental variables are set, you can use one of the approaches from the attached QVW

Re: Reload time in set analysis

swuehl — Fri, 01 Apr 2016 23:16:08 GMT

Could you detail what you mean with 'not working' / 'doesn't work'?

I assume your default timestamp format is matching your TIME field format.

Then, the only difference should be that the second set expression filters on a potentially later timestamp than the first.

Might this cause an issue (well, I assume that's what you are trying to achieve)?

To debug these kind of issues, it's often good to compare the two set expressions after the dollar sign expansions have been expanded.

To do this, create a straight table with these two expressions. Keep the expression labels empty.

Then hover with the mouse over the expression column header, they should now show the expressions, with dollar sign expansions evaluated. Do you see any difference, e.g. in timestamp format, besides the actual timestamp value being different?

Re: Reload time in set analysis

ilyas_coban — Mon, 04 Apr 2016 12:14:29 GMT

But my TIME field contains time stamp. Like: 30.03.2016 10:57:29 AM

Re: Reload time in set analysis

ilyas_coban — Mon, 04 Apr 2016 13:02:08 GMT

Thanks Sunny, but i tried both of them and this code:

Sum({<[TIME] = {">$(=TimeStamp(ReloadTime()-3, 'DD.MM.YYYY hh:mm:ss[.fff] TT'))"} >} Sales)

and got nothing. Results are 0.00. I don't know if it helps but here is my environmental variables:

Also, I downloaded your file and your calculations are 0 as well. Is it possible that my Qlikview version is not supporting this code?

Re: Reload time in set analysis

sunny_talwar — Mon, 04 Apr 2016 13:33:10 GMT

And can you share how you create TIME field in your script?

Re: Reload time in set analysis

ilyas_coban — Mon, 04 Apr 2016 14:02:21 GMT

Hi swuehl,

Thanks so much for the tip. It is very helpful.Your assumption is correct. My TIME field format is matching with timestamp format. I used your tip and looked for pop up and wrote the result (i.e. 26.03.2016 8:49:58 AM) into set analysis and it calculated value correctly. But, still it is showing 0.00 when I write reloadtime() or now(0).

Re: Reload time in set analysis

swuehl — Mon, 04 Apr 2016 14:03:30 GMT

Do you have any other selections made?

You would also get zero returned if your current selections are incompatible with the set defined in your set expression, i.e. no records present in the aggregation scope.

Re: Reload time in set analysis

ilyas_coban — Mon, 04 Apr 2016 14:13:05 GMT

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

Re: Reload time in set analysis

sunny_talwar — Mon, 04 Apr 2016 14:27:42 GMT

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)

Re: Reload time in set analysis

ilyas_coban — Mon, 04 Apr 2016 14:28:49 GMT

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.

Re: Reload time in set analysis

swuehl — Mon, 04 Apr 2016 14:31:46 GMT

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?

Re: Reload time in set analysis

sunny_talwar — Mon, 04 Apr 2016 15:16:36 GMT

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

Re: Reload time in set analysis

swuehl — Mon, 04 Apr 2016 15:17:48 GMT

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