6 Replies Latest reply: May 14, 2017 11:47 PM by Robin Hausdörfer

# T12 Calculaton / Trailing 12 Month Average in Qlik Sense

I am trying to figure out how to use Qlik Sense to do a  T12 calculation

Trying to write the express in Qlik to go from the data set (on the left) to the T12 average per month (on the right)

In Excel, the T12 formula is effectively sum(Month1:Month12)/12.

Any suggestions?

 DATA SET Trailing 12 Months (T12) Month Product Sales \$ Qty Month Product T12 Avg \$ T12 Avg Qty 1/31/2016 TV \$ 525,000 1,500 12/31/2016 TV \$ 1,556,041.67 4,446 2/29/2016 TV \$        1,225,000 3,500 1/31/2017 TV \$  1,614,375.00 4,613 3/31/2016 TV \$            595,000 1,700 2/28/2017 TV \$  1,585,208.33 4,529 4/30/2016 TV \$            700,000 2,000 3/31/2017 TV \$  1,666,875.00 4,763 5/31/2016 TV \$        1,750,000 5,000 4/30/2017 TV \$  1,739,791.67 4,971 6/30/2016 TV \$        1,050,000 3,000 5/31/2017 TV \$  1,696,041.67 4,846 7/31/2016 TV \$        1,575,000 4,500 12/31/2016 Monitor \$      555,833.33 5,558 8/31/2016 TV \$        2,625,000 7,500 1/31/2017 Monitor \$      568,333.33 5,683 9/30/2016 TV \$        2,975,000 8,500 2/28/2017 Monitor \$      560,000.00 5,600 10/31/2016 TV \$        2,852,500 8,150 3/31/2017 Monitor \$      542,500.00 5,425 11/30/2016 TV \$        1,225,000 3,500 4/30/2017 Monitor \$      578,333.33 5,783 12/31/2016 TV \$        1,575,000 4,500 5/31/2017 Monitor \$      563,750.00 5,638 1/31/2017 TV \$        1,225,000 3,500 2/28/2017 TV \$            875,000 2,500 3/31/2017 TV \$        1,575,000 4,500 4/30/2017 TV \$        1,575,000 4,500 5/30/2017 TV \$        1,225,000 3,500 1/31/2016 Monitor \$            200,000 2,000 2/29/2016 Monitor \$            350,000 3,500 3/31/2016 Monitor \$            410,000 4,100 4/30/2016 Monitor \$            320,000 3,200 5/31/2016 Monitor \$            675,000 6,750 6/30/2016 Monitor \$            850,000 8,500 7/31/2016 Monitor \$            950,000 9,500 8/31/2016 Monitor \$            750,000 7,500 9/30/2016 Monitor \$            350,000 3,500 10/31/2016 Monitor \$            970,000 9,700 11/30/2016 Monitor \$            505,000 5,050 12/31/2016 Monitor \$            340,000 3,400 1/31/2017 Monitor \$            350,000 3,500 2/28/2017 Monitor \$            250,000 2,500 3/31/2017 Monitor \$            200,000 2,000 4/30/2017 Monitor \$            750,000 7,500 5/30/2017 Monitor \$            500,000 5,000
• ###### Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense

try something like:

rangeavg(above(sum(Sales),0,12))

--> but you must not change the sorting...

• ###### Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense

Thanks for the tip.  When I did it in table format, it worked as you described if I sort it based on chronological order.

However, when I tried to convert it to Line Graph, the numbers seem to be completely different.

I need to show them in the line or bar graph so that we can spot the trending of each product.

Is there a way to present the results correctly in line or bar graph?

• ###### Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense

May be like attached

If(RowNo() >= 12, RangeAvg(Above(Sum([Sales \$]), 0, 12)))

If(RowNo() >= 12, RangeAvg(Above(Sum(Qty), 0, 12)))

• ###### Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense

This works.  Thanks.   However, how to you show it properly in the sequential order (chronological) on the line graph?

• ###### Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense

May be give this a try

Sum(Aggr(If(RowNo() >= 12, RangeAvg(Above(Sum(Qty), 0, 12))), Product, (Month,(NUMERIC))))