Avg Counts from a table

[#1]

Hi

I'm new to Qlik in particular Qlik Sense so apologies if this is a very basic question.

What I want is an avg of the last 7 days sales.

I have a table which has the following data:

Date	Sales
2017-03-01	3130
2017-03-02	3131
2017-03-03	3131
2017-03-04	3130
2017-03-05	3130
2017-03-06	3130
2017-03-07	3133
AVG	3131

The expression for the dates is simply:

[SaleDate]

The expression to calculate the sales is simply:

count([%SaleID])

In the table I've manually filtered the dates to the last 7 days.

The AVG in the above table is the figure that I want out to be able to calculate in a KPI, however I cant seem to get it to work.

If anyone can help, I would be really grateful.

Andrew

[brunobertels]

Hi Andrew

May be this  :

in a KPI Object

Avg({$<Date={">=$(=max(Date)-7)<=$(=max(Date))"}>}Sales)

As a new member let me advise to have a look to this set analysis wizard :

It will help you a lot

[sunny_talwar]

May be this

Avg({<SaleDate = {"$(='>=' & Date(Max(SaleDate)-7, 'YYYY-MM-DD') & '<=' & Date(Max(SaleDate), 'YYYY-MM-DD'))"}>} Aggr(Count({<SaleDate = {"$(='>=' & Date(Max(SaleDate)-7, 'YYYY-MM-DD') & '<=' & Date(Max(SaleDate), 'YYYY-MM-DD'))"}>}[%SaleID]), SaleDate))

[ahaahaaha]

As variant

Avg({<DateSales={">=$(=Date(Max([DateSales])-7))"}>}Sales)

[karthiksrqv]

You could try this,

rangeavg(

count({<SaleDate = {$(max(SaleDate))}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),2)}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),3)}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),4)}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),5)}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),6)}>} [%SaleID]),

count({<SaleDate = {$(max(SaleDate),7)}>} [%SaleID]),

)

Easy to read when one is starting out.

[#6]

Thank you all for your suggestions, I'm plugging them in now to find out how they work and what suits how we work best.

Thanks again!