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: 
ilyas_coban
Contributor II
Contributor II

Reload time in set analysis

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,

1 Solution

Accepted Solutions
ilyas_coban
Contributor II
Contributor II
Author

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.

View solution in original post

15 Replies
dmac1971
Creator III
Creator III

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?

sunny_talwar

Can you try this:

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

sunny_talwar

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

Capture.PNG

Capture.PNG

swuehl
MVP
MVP

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?

ilyas_coban
Contributor II
Contributor II
Author

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

ilyas_coban
Contributor II
Contributor II
Author

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:

environmental variables.PNG

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

sunny_talwar

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

ilyas_coban
Contributor II
Contributor II
Author

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

swuehl
MVP
MVP

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.