Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

5 Replies
brunobertels
Valued Contributor II

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 :

It will help you a lot

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

ahaahaaha
Honored Contributor

Re: Avg Counts from a table

As variant

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

karthiksrqv
Contributor II

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.

Not applicable

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!