topic Ave Function in QlikView https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458176#M795836 <HTML><HEAD></HEAD><BODY><P>Hi everyone!</P><P>I would like to Cost by Sale ratio for every main account, my scenario is something like this;</P><P>&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" width="345"><TBODY><TR><TD class="xl63" height="20" width="64">year</TD><TD class="xl63" style="border-left: none;" width="64">month</TD><TD class="xl63" style="border-left: none;" width="89">main account</TD><TD class="xl63" style="border-left: none;" width="64">cost</TD><TD class="xl63" style="border-left: none;" width="64">sales</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">1</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">120</TD><TD class="xl63" style="border-top: none; border-left: none;">800</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">2</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">125</TD><TD class="xl63" style="border-top: none; border-left: none;">820</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">3</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">130</TD><TD class="xl63" style="border-top: none; border-left: none;">845</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">4</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">95</TD><TD class="xl63" style="border-top: none; border-left: none;">700</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">5</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">124</TD><TD class="xl63" style="border-top: none; border-left: none;">810</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">6</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">132</TD><TD class="xl63" style="border-top: none; border-left: none;">825</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">7</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">145</TD><TD class="xl63" style="border-top: none; border-left: none;">845</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">8</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">127</TD><TD class="xl63" style="border-top: none; border-left: none;">801</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">9</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">135</TD><TD class="xl63" style="border-top: none; border-left: none;">850</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">10</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">128</TD><TD class="xl63" style="border-top: none; border-left: none;">825</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">11</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">160</TD><TD class="xl63" style="border-top: none; border-left: none;">900</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">12</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">140</TD><TD class="xl63" style="border-top: none; border-left: none;">890</TD></TR></TBODY></TABLE><P></P><P>how to right below expressions in a straight table?</P><P>&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" width="244"><TBODY><TR><TD class="xl65" height="20" width="81">cost/Sales%</TD><TD class="xl65" style="border-left: none;" width="64">WA%</TD><TD class="xl65" style="border-left: none;" width="99">diff from WA%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">-1%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">14%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">-2%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">17%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">1%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">18%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">2%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR></TBODY></TABLE></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT Anonymous 2020-11-25T16:16:04Z Ave Function https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458176#M795836 <HTML><HEAD></HEAD><BODY><P>Hi everyone!</P><P>I would like to Cost by Sale ratio for every main account, my scenario is something like this;</P><P>&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" width="345"><TBODY><TR><TD class="xl63" height="20" width="64">year</TD><TD class="xl63" style="border-left: none;" width="64">month</TD><TD class="xl63" style="border-left: none;" width="89">main account</TD><TD class="xl63" style="border-left: none;" width="64">cost</TD><TD class="xl63" style="border-left: none;" width="64">sales</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">1</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">120</TD><TD class="xl63" style="border-top: none; border-left: none;">800</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">2</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">125</TD><TD class="xl63" style="border-top: none; border-left: none;">820</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">3</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">130</TD><TD class="xl63" style="border-top: none; border-left: none;">845</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">4</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">95</TD><TD class="xl63" style="border-top: none; border-left: none;">700</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">5</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">124</TD><TD class="xl63" style="border-top: none; border-left: none;">810</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">6</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">132</TD><TD class="xl63" style="border-top: none; border-left: none;">825</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">7</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">145</TD><TD class="xl63" style="border-top: none; border-left: none;">845</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">8</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">127</TD><TD class="xl63" style="border-top: none; border-left: none;">801</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">9</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">135</TD><TD class="xl63" style="border-top: none; border-left: none;">850</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">10</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">128</TD><TD class="xl63" style="border-top: none; border-left: none;">825</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">11</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">160</TD><TD class="xl63" style="border-top: none; border-left: none;">900</TD></TR><TR><TD class="xl63" height="20" style="border-top: none;">2017</TD><TD class="xl63" style="border-top: none; border-left: none;">12</TD><TD class="xl63" style="border-top: none; border-left: none;">100</TD><TD class="xl63" style="border-top: none; border-left: none;">140</TD><TD class="xl63" style="border-top: none; border-left: none;">890</TD></TR></TBODY></TABLE><P></P><P>how to right below expressions in a straight table?</P><P>&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" width="244"><TBODY><TR><TD class="xl65" height="20" width="81">cost/Sales%</TD><TD class="xl65" style="border-left: none;" width="64">WA%</TD><TD class="xl65" style="border-left: none;" width="99">diff from WA%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">-1%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">14%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">-2%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">15%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">17%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">1%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">18%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">2%</TD></TR><TR><TD align="right" class="xl66" height="20" style="border-top: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">16%</TD><TD align="right" class="xl67" style="border-top: none; border-left: none;">0%</TD></TR></TBODY></TABLE></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458176#M795836 Anonymous 2020-11-25T16:16:04Z Re: Ave Function https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458177#M795837 <HTML><HEAD></HEAD><BODY><P>Sorry to compute <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">Cost by Sale ratio</SPAN></P></BODY></HTML> Wed, 03 Jan 2018 19:11:25 GMT https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458177#M795837 Anonymous 2018-01-03T19:11:25Z Re: Ave Function https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458178#M795838 <HTML><HEAD></HEAD><BODY><P>This</P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/188807_Capture.PNG" style="height: 163px; width: 620px;" /></P><P></P><P>Expressions</P><P><STRONG>=Sum(cost)/Sum(sales)</STRONG></P><P><STRONG>=Avg(TOTAL Aggr(Sum(cost)/Sum(sales), year, month))</STRONG></P><P><STRONG>=Column(1) - Column(2)</STRONG></P></BODY></HTML> Wed, 03 Jan 2018 19:13:47 GMT https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458178#M795838 sunny_talwar 2018-01-03T19:13:47Z Re: Ave Function https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458179#M795839 <HTML><HEAD></HEAD><BODY><P>The second expression might change if you need a total average by main account</P><P><STRONG style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=Avg(TOTAL <SPAN style="color: #ff0000;">&lt;[main account]&gt;</SPAN> Aggr(Sum(cost)/Sum(sales), year, month))</STRONG></P></BODY></HTML> Wed, 03 Jan 2018 19:15:04 GMT https://community.qlik.com/t5/QlikView/Ave-Function/m-p/1458179#M795839 sunny_talwar 2018-01-03T19:15:04Z