Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey again guys,
I'm now working on a 90 day rolling average.
I've tried a variety of solutions listed on here, but maybe using the fields I am working with, I have messed up the syntax (I'm hoping it's something as simple as that).
I've tried using these equations:
Set Analysis for certain Point in Time
More specifically, the one I have been working with the most is:
Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )
Here's what I have adapted
Sum( {<Date={‘>=$(=Date(Today()-89))<=$(=Today())’}>} [Line Amount] )
So I'm trying to total up the Line Amount field for the last 90 days. I get null values.
I have a couple other places to use this within an if statement as well.
Any help would be greatly appreciated.
Cheers,
Troy
Message was edited by: Troy Strachan July 8 2016
Have you made sure that your Date format matches that for the date format that comes out when you use Date(Today() - 89)?
Try this:
Sum({<Date={"$(='>=' & Date(Today()-89, 'DateFieldFormatHere') & '<=' & Date(Today(), 'DateFieldFormatHere))"}>} [Line Amount])
Read about dates in set analysis here:
Still getting a 0 value.
When I do Date(Today()-89) I'm getting 4/9/2016 which is the correct date format
Can you share a sample or a screenshot atleast?
What exactly do you want to see? the date format? the 0 value?
I can't share the app based on our corporate policy.
On the gauge this is the expression:
Sum({<Date={"$(='>=' & Date(Today()-89, '[Date]') & '<=' & Date(Today(), '[Date]))"}>} [Line Amount])
Hey Sunny, I have just attached a data sample in a qlik sense file. Can you take a look and let me know what I'm doing wrong?
Check these changes in red
Sum({<[[Posting Date]]] ={"$(='>=' & Date(Today()-89, 'DD/MM/YYYY') & '<=' & Date(Today(), 'DD/MM/YYYY'))"}>} [sum(Actual)])
What is odd is that it works with the test data but doesn't work with my production data.
Might be because of [[Posting Date]]] you might just need one set of square brackets -> [Posting Date]