Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to calculate the average total of 12 months

Hi ,

I have the data as below , product numbers and the sales for the 12 months . I need to calculate the last column "Average of 12 months" ,a single column which is a total of avg of columns from jan 18 to Dec 18 for each product number. I am not knowing how to calculate this in Qlikview.Could anyone let me know

Regards,

Rekha

1 Solution

Accepted Solutions
michaelsikora
Contributor III
Contributor III

Rekha,

The expression I gave you above will only work for the average. You will have to have a different expression for each month and also the total column. 15 columns (including your dimension)

Current month:

=sum({$<Year={"<=$(=Max(Year))"}MonthName={">=$(=addmonths(today(), -1)) <=$(=addmonths(today(), -0))"}>}NetSales))

Change the underlined bold above to -1 for last month and -2 month before that and so on... until you get -11

Then total is:

sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -12)) <=$(=addmonths(today(), -1))"}>}NetSalesDOM)


Then average

sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -12)) <=$(=addmonths(today(), -1))"}>}NetSalesDOM)/12

View solution in original post

6 Replies
michaelsikora
Contributor III
Contributor III

The following solution will give you the rolling 12 month average. Use product number as dimension and use the following as the expression.


sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -12)) <=$(=addmonths(today(), -1))"}>}NetSalesDOM) /12

Anonymous
Not applicable
Author

Thank you Michael for replying.

I tried putting your expression , i get results as below:

This is because i have removed the Month year dimension. I want the view as below

I want the product number and the sum of sales for individual 12 months ,then the total for 12 months single column and the average of 12 months single column .

When i consider the product and the Month year as dimension , then i get results upto total for 12 months . When i try to calculate the average then its being considered per month average not coming up as a single column . I am unable to get the average as single column .

Anonymous
Not applicable
Author

stalwar1swuehl‌ Could you please have a look at the issue I am facing and help out

michaelsikora
Contributor III
Contributor III

Rekha,

The expression I gave you above will only work for the average. You will have to have a different expression for each month and also the total column. 15 columns (including your dimension)

Current month:

=sum({$<Year={"<=$(=Max(Year))"}MonthName={">=$(=addmonths(today(), -1)) <=$(=addmonths(today(), -0))"}>}NetSales))

Change the underlined bold above to -1 for last month and -2 month before that and so on... until you get -11

Then total is:

sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -12)) <=$(=addmonths(today(), -1))"}>}NetSalesDOM)


Then average

sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -12)) <=$(=addmonths(today(), -1))"}>}NetSalesDOM)/12

neelamsaroha157
Specialist II
Specialist II

Did you try directly using the 'Avg' function?

Avg({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(max(dateField), -12)) <=$(=addmonths(max(dateField), -1))"}>}YourMeasureField)


Please provide a sample and expected result for more deep dive.

Anonymous
Not applicable
Author

Thank you Michael. I tried using diff exp for each month and it works now!