90 Day rolling average

[shearwatertroy]

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

[sunny_talwar]

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:

Dates in Set Analysis

[shearwatertroy]

Still getting a 0 value.

When I do Date(Today()-89) I'm getting 4/9/2016 which is the correct date format

[sunny_talwar]

Can you share a sample or a screenshot atleast?

[shearwatertroy]

What exactly do you want to see? the date format? the 0 value?

I can't share the app based on our corporate policy.

[shearwatertroy]

On the gauge this is the expression:

Sum({<Date={"$(='>=' & Date(Today()-89, '[Date]') & '<=' & Date(Today(), '[Date]))"}>} [Line Amount])

[shearwatertroy]

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?

[sunny_talwar]

Check these changes in red

Sum({<[[Posting Date]]] ={"$(='>=' & Date(Today()-89, 'DD/MM/YYYY') & '<=' & Date(Today(), 'DD/MM/YYYY'))"}>} [sum(Actual)])

[shearwatertroy]

What is odd is that it works with the test data but doesn't work with my production data.

[sunny_talwar]

Might be because of [[Posting Date]]] you might just need one set of square brackets -> [Posting Date]