5 Replies Latest reply: Mar 9, 2017 10:26 AM by Andrew Jenkins

# Avg Counts from a table

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:

DateSales
2017-03-013130
2017-03-023131
2017-03-033131
2017-03-043130
2017-03-053130
2017-03-063130
2017-03-073133
AVG3131

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

• ###### Re: Avg Counts from a table

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 :

• ###### Re: Avg Counts from a table

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

• ###### Re: Avg Counts from a table

As variant

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

• ###### Re: Avg Counts from a table

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.

• ###### Re: Avg Counts from a table

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!