Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
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?
Can you try this:
Sum({<[TIME] = {">$(=TimeStamp(ReloadTime()-3, 'DD.MM.YYYY hh:mm:ss TT'))"} >} Sales)
Based on how your environmental variables are set, you can use one of the approaches from the attached QVW
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?
But my TIME field contains time stamp. Like: 30.03.2016 10:57:29 AM
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?
And can you share how you create TIME field in your script?
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).
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.