Max Date -7 , total value

[#1]

Hello all,

I am using a Combo chart,

     as Dimensions I have Date

          and Expressions I have count IssueNo and another expression Sum of Minutes taken each issue each day.

I am trying to add a Line that shows the Average of IssueNo's for the last 7 Days

ex;

Count of IssueNo     Date

1                              01/02/2017

2                              02/02/2017

3                              03/02/2017

4                              04/02/2017

5                              05/02/2017

6                              06/02/2017

7                              07/02/2017

8                              08/02/2017    

9                              09/02/2017         

10                           10/02/2017

last 7 days there's a 24 number of IssueNo's divide that by 7 days the average number of IssueNo's will be 4

How can I do this is a Combo Chart, please help

Many thanks,

Amir

[Carlos_Reyes]

You could use something like:

Avg( {<Date={'>=$(=Max(Date)-6)<=$(=Max(Date))'}>} Total

  Aggr(

  Sum( {<Date={'>=$(=Max(Date)-6)<=$(=Max(Date))'}>} [Count of IssueNo])

  ,Date)

)

Review the enclosed file for the example.

[#3]

Thank you Carlos for your help

But I want the report to look like the attached

Is it possible?

Thanks

[Carlos_Reyes · Accepted solution]

Not exactly the same but you could do something similar with a combo chart.

Review the example.

[#5]

Hello Carlos,

Thank you for your help, I was looking for a rolling 7 days average, and I have found the answer to my question

which is the below

rangeavg(above(Count(ColumnName),0,7))

[Carlos_Reyes]

That's another way to calculate. I'm glad you found exactly what you were looking for.

On my example you don't necessarily need to use Date as the Dimension in your chart. Because you define the avg based on the aggr by Date in the calculation. So... this is an alternative way to achieve the same result. Hope you found it useful.

Regards,