Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

shearwatertroy
New Contributor III

90 Day rolling average

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

11 Replies
MVP
MVP

Re: 90 Day rolling average

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

Re: 90 Day rolling average

Still getting a 0 value.

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

MVP
MVP

Re: 90 Day rolling average

Can you share a sample or a screenshot atleast?

shearwatertroy
New Contributor III

Re: 90 Day rolling average

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

Re: 90 Day rolling average

On the gauge this is the expression:

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

qlik90day1.PNG

shearwatertroy
New Contributor III

Re: 90 Day rolling average

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?

MVP
MVP

Re: 90 Day rolling average

Check these changes in red

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


Capture.PNG

shearwatertroy
New Contributor III

Re: 90 Day rolling average

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

MVP
MVP

Re: 90 Day rolling average

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