Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!