Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 .
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
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.
Thank you Michael. I tried using diff exp for each month and it works now!