# Qlik Sense App Development

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 )

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

Tags (2)
11 Replies
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])

Dates in Set Analysis

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

## Re: 90 Day rolling average

Can you share a sample or a screenshot atleast?

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.

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])

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

## 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)])

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

## Re: 90 Day rolling average

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