Qlik Community

New to Qlik Sense

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

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Askquestion
Contributor
Contributor

Aggregation Function

I have a column of years, months and a column of temperature of every month 

I need to make the avarage between the same month of diferents years 

Example:

The avarage of all the Januarys, Februarys.....

Labels (4)
8 Replies
Taoufiq_Zarra

@Askquestioncan you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Askquestion
Contributor
Contributor
Author

Month  |  Temperature

Jan/20 |        10

Fev/20 |          15

Jan/21 |          20

Fev/21 |          25

Output: 

Month  |  Temperature | Month Avarage

Jan/20 |        10                 |             15

Fev/20 |          15                |              20

Jan/21 |          20               |              15

Fev/21 |          25               |                20

stevejoyce
Specialist II
Specialist II

 

data:
load
Date#(Month, 'MMM/YYYY') as MonthYear
,Month(Date#(Month, 'MMM/YYYY')) as Month
,Temperature;

load * inline [
Month, Temperature

Jan/20, 10

Feb/20, 15

Jan/21, 20

Feb/21, 25
];

 

left join (data)
load
Month,
avg(Temperature) as Month_Average
resident data
group by Month;

Askquestion
Contributor
Contributor
Author

Very thank you it worked!

But there's a problem I forgot to say

I have 2 diferents cities and I need to calculate their months saparely with this method it made the avarage of both how can I fix it?

stevejoyce
Specialist II
Specialist II

left join (data)
load
Month,

City,
avg(Temperature) as Month_Average
resident data
group by Month, City;

Askquestion
Contributor
Contributor
Author

It worked very well thank you so much!!!!

 

saniyask
Creator
Creator

you can simply load the data in the script and use aggr in your expression.

Avg(Aggr(Sum(Temp),Month,City)) this will work

saniyask_0-1630324811484.jpeg

 

saniyask_1-1630324816101.jpeg

 

 

Saniya.

stevejoyce
Specialist II
Specialist II

Great.  If you can please mark solution(s) so thread can be marked appropriately.