topic Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense in Visualization and Usability

topic Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense in Visualization and Usability https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339958#M96256 <HTML><HEAD></HEAD><BODY><P>Thanks for the tip.  When I did it in table format, it worked as you described if I sort it based on chronological order.</P><P>However, when I tried to convert it to Line Graph, the numbers seem to be completely different.</P><P>I need to show them in the line or bar graph so that we can spot the trending of each product.  </P><P>Is there a way to present the results correctly in line or bar graph?</P><P></P><P></P><P><IMG alt="T12.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/163292_T12.PNG" style="height: 382px; width: 620px;" /></P></BODY></HTML> Sun, 14 May 2017 20:39:11 GMT 2017-05-14T20:39:11Z T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339955#M96253 <HTML><HEAD></HEAD><BODY><P>I am trying to figure out how to use Qlik Sense to do a  T12 calculation</P><P>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)</P><P>In Excel, the T12 formula is effectively sum(Month1:Month12)/12.</P><P></P><P>Any suggestions?</P><P></P><P>          </P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 738px;"><TBODY><TR><TD class="xl65" colspan="4" height="21" width="333"><STRONG>DATA SET</STRONG></TD><TD class="xl65" colspan="4" width="341"><STRONG>Trailing 12 Months (T12)</STRONG></TD></TR><TR><TD class="xl66" height="21">Month</TD><TD class="xl67">Product</TD><TD class="xl68"> Sales $ </TD><TD class="xl69"> Qty </TD><TD class="xl66">Month</TD><TD class="xl67">Product</TD><TD class="xl68"> T12 Avg $ </TD><TD class="xl69"> T12 Avg Qty </TD></TR><TR><TD align="right" class="xl70" height="20" style="border-top: none;">1/31/2016</TD><TD class="xl71" style="border-top: none;">TV</TD><TD class="xl72" style="border-top: none;"> $ 525,000 </TD><TD class="xl73" style="border-top: none;">         1,500 </TD><TD align="right" class="xl70" style="border-top: none;">12/31/2016</TD><TD class="xl80" style="border-top: none;">TV</TD><TD class="xl81" style="border-top: none;"> $ 1,556,041.67 </TD><TD class="xl73" style="border-top: none;">                   4,446 </TD></TR><TR><TD align="right" class="xl74" height="20">2/29/2016</TD><TD>TV</TD><TD class="xl63"> $        1,225,000 </TD><TD class="xl75">         3,500 </TD><TD align="right" class="xl74">1/31/2017</TD><TD class="xl82">TV</TD><TD class="xl83"> $  1,614,375.00 </TD><TD class="xl75">                   4,613 </TD></TR><TR><TD align="right" class="xl74" height="20">3/31/2016</TD><TD>TV</TD><TD class="xl63"> $            595,000 </TD><TD class="xl75">         1,700 </TD><TD align="right" class="xl74">2/28/2017</TD><TD class="xl82">TV</TD><TD class="xl83"> $  1,585,208.33 </TD><TD class="xl75">                   4,529 </TD></TR><TR><TD align="right" class="xl74" height="20">4/30/2016</TD><TD>TV</TD><TD class="xl63"> $            700,000 </TD><TD class="xl75">         2,000 </TD><TD align="right" class="xl74">3/31/2017</TD><TD class="xl82">TV</TD><TD class="xl83"> $  1,666,875.00 </TD><TD class="xl75">                   4,763 </TD></TR><TR><TD align="right" class="xl74" height="20">5/31/2016</TD><TD>TV</TD><TD class="xl63"> $        1,750,000 </TD><TD class="xl75">         5,000 </TD><TD align="right" class="xl74">4/30/2017</TD><TD class="xl82">TV</TD><TD class="xl83"> $  1,739,791.67 </TD><TD class="xl75">                   4,971 </TD></TR><TR><TD align="right" class="xl74" height="20">6/30/2016</TD><TD>TV</TD><TD class="xl63"> $        1,050,000 </TD><TD class="xl75">         3,000 </TD><TD align="right" class="xl74">5/31/2017</TD><TD class="xl82">TV</TD><TD class="xl83"> $  1,696,041.67 </TD><TD class="xl75">                   4,846 </TD></TR><TR><TD align="right" class="xl74" height="20">7/31/2016</TD><TD>TV</TD><TD class="xl63"> $        1,575,000 </TD><TD class="xl75">         4,500 </TD><TD align="right" class="xl74">12/31/2016</TD><TD class="xl82">Monitor</TD><TD class="xl83"> $      555,833.33 </TD><TD class="xl75">                   5,558 </TD></TR><TR><TD align="right" class="xl74" height="20">8/31/2016</TD><TD>TV</TD><TD class="xl63"> $        2,625,000 </TD><TD class="xl75">         7,500 </TD><TD align="right" class="xl74">1/31/2017</TD><TD class="xl82">Monitor</TD><TD class="xl83"> $      568,333.33 </TD><TD class="xl75">                   5,683 </TD></TR><TR><TD align="right" class="xl74" height="20">9/30/2016</TD><TD>TV</TD><TD class="xl63"> $        2,975,000 </TD><TD class="xl75">         8,500 </TD><TD align="right" class="xl74">2/28/2017</TD><TD class="xl82">Monitor</TD><TD class="xl83"> $      560,000.00 </TD><TD class="xl75">                   5,600 </TD></TR><TR><TD align="right" class="xl74" height="20">10/31/2016</TD><TD>TV</TD><TD class="xl63"> $        2,852,500 </TD><TD class="xl75">         8,150 </TD><TD align="right" class="xl74">3/31/2017</TD><TD class="xl82">Monitor</TD><TD class="xl83"> $      542,500.00 </TD><TD class="xl75">                   5,425 </TD></TR><TR><TD align="right" class="xl74" height="20">11/30/2016</TD><TD>TV</TD><TD class="xl63"> $        1,225,000 </TD><TD class="xl75">         3,500 </TD><TD align="right" class="xl74">4/30/2017</TD><TD class="xl82">Monitor</TD><TD class="xl83"> $      578,333.33 </TD><TD class="xl75">                   5,783 </TD></TR><TR><TD align="right" class="xl74" height="21">12/31/2016</TD><TD>TV</TD><TD class="xl63"> $        1,575,000 </TD><TD class="xl75">         4,500 </TD><TD align="right" class="xl76">5/31/2017</TD><TD class="xl84">Monitor</TD><TD class="xl85"> $      563,750.00 </TD><TD class="xl79">                   5,638 </TD></TR><TR><TD align="right" class="xl74" height="20">1/31/2017</TD><TD>TV</TD><TD class="xl63"> $        1,225,000 </TD><TD class="xl75">         3,500 </TD></TR><TR><TD align="right" class="xl74" height="20">2/28/2017</TD><TD>TV</TD><TD class="xl63"> $            875,000 </TD><TD class="xl75">         2,500 </TD></TR><TR><TD align="right" class="xl74" height="20">3/31/2017</TD><TD>TV</TD><TD class="xl63"> $        1,575,000 </TD><TD class="xl75">         4,500 </TD></TR><TR><TD align="right" class="xl74" height="20">4/30/2017</TD><TD>TV</TD><TD class="xl63"> $        1,575,000 </TD><TD class="xl75">         4,500 </TD></TR><TR><TD align="right" class="xl74" height="20">5/30/2017</TD><TD>TV</TD><TD class="xl63"> $        1,225,000 </TD><TD class="xl75">         3,500 </TD></TR><TR><TD align="right" class="xl74" height="20">1/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            200,000 </TD><TD class="xl75">         2,000 </TD></TR><TR><TD align="right" class="xl74" height="20">2/29/2016</TD><TD>Monitor</TD><TD class="xl63"> $            350,000 </TD><TD class="xl75">         3,500 </TD></TR><TR><TD align="right" class="xl74" height="20">3/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            410,000 </TD><TD class="xl75">         4,100 </TD></TR><TR><TD align="right" class="xl74" height="20">4/30/2016</TD><TD>Monitor</TD><TD class="xl63"> $            320,000 </TD><TD class="xl75">         3,200 </TD></TR><TR><TD align="right" class="xl74" height="20">5/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            675,000 </TD><TD class="xl75">         6,750 </TD></TR><TR><TD align="right" class="xl74" height="20">6/30/2016</TD><TD>Monitor</TD><TD class="xl63"> $            850,000 </TD><TD class="xl75">         8,500 </TD></TR><TR><TD align="right" class="xl74" height="20">7/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            950,000 </TD><TD class="xl75">         9,500 </TD></TR><TR><TD align="right" class="xl74" height="20">8/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            750,000 </TD><TD class="xl75">         7,500 </TD></TR><TR><TD align="right" class="xl74" height="20">9/30/2016</TD><TD>Monitor</TD><TD class="xl63"> $            350,000 </TD><TD class="xl75">         3,500 </TD></TR><TR><TD align="right" class="xl74" height="20">10/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            970,000 </TD><TD class="xl75">         9,700 </TD></TR><TR><TD align="right" class="xl74" height="20">11/30/2016</TD><TD>Monitor</TD><TD class="xl63"> $            505,000 </TD><TD class="xl75">         5,050 </TD></TR><TR><TD align="right" class="xl74" height="20">12/31/2016</TD><TD>Monitor</TD><TD class="xl63"> $            340,000 </TD><TD class="xl75">         3,400 </TD></TR><TR><TD align="right" class="xl74" height="20">1/31/2017</TD><TD>Monitor</TD><TD class="xl63"> $            350,000 </TD><TD class="xl75">         3,500 </TD></TR><TR><TD align="right" class="xl74" height="20">2/28/2017</TD><TD>Monitor</TD><TD class="xl63"> $            250,000 </TD><TD class="xl75">         2,500 </TD></TR><TR><TD align="right" class="xl74" height="20">3/31/2017</TD><TD>Monitor</TD><TD class="xl63"> $            200,000 </TD><TD class="xl75">         2,000 </TD></TR><TR><TD align="right" class="xl74" height="20">4/30/2017</TD><TD>Monitor</TD><TD class="xl63"> $            750,000 </TD><TD class="xl75">         7,500 </TD></TR><TR><TD align="right" class="xl76" height="21">5/30/2017</TD><TD class="xl77">Monitor</TD><TD class="xl78"> $            500,000 </TD><TD class="xl79">         5,000 </TD></TR></TBODY></TABLE></BODY></HTML> Sat, 13 May 2017 04:32:11 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339955#M96253 2017-05-13T04:32:11Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339956#M96254 <HTML><HEAD></HEAD><BODY><P>try something like:</P><P></P><P>rangeavg(above(sum(Sales),0,12))</P><P></P><P>--> but you must not change the sorting...</P></BODY></HTML> Sun, 14 May 2017 09:28:39 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339956#M96254 Anonymous 2017-05-14T09:28:39Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339957#M96255 <HTML><HEAD></HEAD><BODY><P>May be like attached</P><P><STRONG>If(RowNo() >= 12, RangeAvg(Above(Sum([Sales $]), 0, 12)))</STRONG></P><P><STRONG>If(RowNo() >= 12, RangeAvg(Above(Sum(Qty), 0, 12)))</STRONG></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/163283_Capture.PNG" style="height: 307px; width: 620px;" /></P></BODY></HTML> Sun, 14 May 2017 14:48:25 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339957#M96255 sunny_talwar 2017-05-14T14:48:25Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339958#M96256 <HTML><HEAD></HEAD><BODY><P>Thanks for the tip.  When I did it in table format, it worked as you described if I sort it based on chronological order.</P><P>However, when I tried to convert it to Line Graph, the numbers seem to be completely different.</P><P>I need to show them in the line or bar graph so that we can spot the trending of each product.  </P><P>Is there a way to present the results correctly in line or bar graph?</P><P></P><P></P><P><IMG alt="T12.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/163292_T12.PNG" style="height: 382px; width: 620px;" /></P></BODY></HTML> Sun, 14 May 2017 20:39:11 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339958#M96256 2017-05-14T20:39:11Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339959#M96257 <HTML><HEAD></HEAD><BODY><P>This works.  Thanks.   However, how to you show it properly in the sequential order (chronological) on the line graph?</P></BODY></HTML> Sun, 14 May 2017 21:41:12 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339959#M96257 2017-05-14T21:41:12Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339960#M96258 <HTML><HEAD></HEAD><BODY><P>May be give this a try</P><P><STRONG>Sum(Aggr(If(RowNo() >= 12, RangeAvg(Above(Sum(Qty), 0, 12))), Product, (Month,(NUMERIC))))</STRONG></P></BODY></HTML> Mon, 15 May 2017 00:36:20 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339960#M96258 sunny_talwar 2017-05-15T00:36:20Z Re: T12 Calculaton / Trailing 12 Month Average in Qlik Sense https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339961#M96259 <HTML><HEAD></HEAD><BODY><P>...use sortable aggr()</P><P><A href="https://community.qlik.com/docs/DOC-18130">Running totals per dimension (RangeSum(), sortable Aggr()...)</A></P></BODY></HTML> Mon, 15 May 2017 03:47:42 GMT https://community.qlik.com/t5/Visualization-and-Usability/T12-Calculaton-Trailing-12-Month-Average-in-Qlik-Sense/m-p/1339961#M96259 Anonymous 2017-05-15T03:47:42Z