topic Re: 90 Day rolling average in App Development

90 Day rolling average

shearwatertroy — Sat, 16 Nov 2024 10:31:35 GMT

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

Re: 90 Day rolling average

sunny_talwar — Thu, 07 Jul 2016 20:48:28 GMT

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

Re: 90 Day rolling average

shearwatertroy — Thu, 07 Jul 2016 20:54:47 GMT

Still getting a 0 value.

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

Re: 90 Day rolling average

sunny_talwar — Thu, 07 Jul 2016 21:04:00 GMT

Can you share a sample or a screenshot atleast?

Re: 90 Day rolling average

shearwatertroy — Thu, 07 Jul 2016 21:13:04 GMT

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

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

Re: 90 Day rolling average

shearwatertroy — Thu, 07 Jul 2016 21:18:21 GMT

On the gauge this is the expression:

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

Re: 90 Day rolling average

shearwatertroy — Fri, 08 Jul 2016 17:00:57 GMT

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?

Re: 90 Day rolling average

sunny_talwar — Fri, 08 Jul 2016 17:11:22 GMT

Check these changes in red

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

Re: 90 Day rolling average

shearwatertroy — Fri, 08 Jul 2016 18:13:14 GMT

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

Re: 90 Day rolling average

sunny_talwar — Fri, 08 Jul 2016 18:16:43 GMT

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

Re: 90 Day rolling average

shearwatertroy — Fri, 08 Jul 2016 18:21:46 GMT

I've already tried that.

Could it be due to having multiple account numbers and they are cancelling each other out? I'm grasping at straws here. 😕 I have to use this equation multiple times on a range of AccountNum

Re: 90 Day rolling average

sunny_talwar — Fri, 08 Jul 2016 18:57:20 GMT

To tell you the truth, I have no idea and it would be difficult to comment without looking at it. I am really sorry. I want to help, but not really sure what I can do

Re: 90 Day rolling average

harithadogiparthi505 — Wed, 28 Sep 2022 13:11:28 GMT

Hello Sunny,

I am getting an error when representing 90 day rolling average in pivot table.

avg(aggr(avg({<date={">=$(=date(max(date)-90,'MM/DD/YYYY')) <=$(=date(max(date),'MM/DD/YYYY'))"}>}COUNT),[File Number],[Day]))

when selecting one week dates in date range picker, the values were good enough.

when selecting 2 weeks in date range picker the first week values are shown but wrong values and second week values are showing null in pivot table.

Kindly help me in resolving this issue when calculating 90 days rolling average.

Any help greatly appreciated.