Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ilyas_coban
New 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
New Contributor II

Re: Reload time in set analysis

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.

15 Replies
dmac1971
Contributor II

Re: Reload time in set analysis

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

Can you try this:

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

Re: Reload time in set analysis

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

Capture.PNG

Capture.PNG

MVP
MVP

Re: Reload time in set analysis

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
New Contributor II

Re: Reload time in set analysis

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

ilyas_coban
New Contributor II

Re: Reload time in set analysis

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?

Re: Reload time in set analysis

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

ilyas_coban
New Contributor II

Re: Reload time in set analysis

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

MVP
MVP

Re: Reload time in set analysis

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.