Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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)

Partner
Partner

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!