topic Re: Max Date -7 , total value in QlikView

Max Date -7 , total value

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max Date -7 , total value

Carlos_Reyes — Fri, 17 Feb 2017 18:48:11 GMT

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.

Re: Max Date -7 , total value

Mon, 20 Feb 2017 09:27:07 GMT

Thank you Carlos for your help

But I want the report to look like the attached

Is it possible?

Thanks

Re: Max Date -7 , total value

Carlos_Reyes — Mon, 20 Feb 2017 16:22:42 GMT

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

Review the example.

Re: Max Date -7 , total value

Mon, 06 Mar 2017 08:44:32 GMT

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

Re: Max Date -7 , total value

Carlos_Reyes — Mon, 06 Mar 2017 17:55:52 GMT

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,