Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paweln
Contributor III
Contributor III

Agregation on only first few values

Dear All,

imagine a table like this:

paweln_0-1602592310886.png

 

I have to:

1. Calculate  average on first (the oldest) 5 values.

2. Calculate average for first (the oldest) 2 values for specific month, lets say March.

dates will be changing so I can't simply use set expression and fix dates to get first values.

3 Replies
Chanty4u
Champion III
Champion III

try this

 for first   "=aggr(if(rank(avg(value))<=5,Date),Date) "

for second:  "=aggr(if(rank(avg(value))<=2,Month),Month) "    ( month you need to derive from date field)

paweln
Contributor III
Contributor III

I have tried the first one so far but it looks like it returns date, not average value.

sunny_talwar

@paweln May be this for first one

Avg(Aggr(If(date >= Max(TOTAL date, 5), value), date)

For Second, will you be selecting a month or do you want it to be passed in as set analysis?