topic Re: Chart Monthly Average over Weekly data in App Development https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476523#M100287 <P>Vegar!&nbsp;Thank you!!<BR />I was so close in the formula I built and your answer helped me to tweak and get to the visualization I needed.<BR /><BR />I hope this solution helps someone else as well!<BR /><BR />FYI, I used this explanation of the AGGR functions NODISTINCT attribute:<BR /><A href="https://community.qlik.com/t5/QlikView-App-Dev/What-NODISTINCT-parameter-does-in-AGGR-function/td-p/368153" target="_blank">https://community.qlik.com/t5/QlikView-App-Dev/What-NODISTINCT-parameter-does-in-AGGR-function/td-p/368153</A></P> Thu, 15 Aug 2024 16:40:02 GMT SheilaBI 2024-08-15T16:40:02Z Chart Monthly Average over Weekly data https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476389#M100272 <P>I have been trying to solve this challenge for a couple days now.&nbsp; Your help is appreciated.<BR /><BR />I am charting performance against an index using a Line Chart.&nbsp; The index data was straight forward to plot.</P> <P>The sales/weight&nbsp; performance data was also straightforward, however it has a lot of noise and so a monthly average is desired in addition.&nbsp;&nbsp;</P> <P>I have not been able to create the monthly average line correctly.&nbsp; Here is a sample of what the final chart should look like (the numbers are not in line with the data in the data table below).</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="SheilaBI_0-1723674076929.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/170645i3A7481FD51C3775C/image-size/medium?v=v2&amp;px=400" role="button" title="SheilaBI_0-1723674076929.png" alt="SheilaBI_0-1723674076929.png" /></span></P> <P>&nbsp;</P> <P>A sample data table is below.</P> <P>1)&nbsp; The data where the x-axis/line dimension is weeks (called Pricing Week in the data table).</P> <P>2)&nbsp; There are three measures for this chart.</P> <P>a)&nbsp; &nbsp;National Index&nbsp; (blue line)&nbsp;&nbsp;</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUM(IF([Value Type]= 'Index' , [Sales]))</P> <P>b)&nbsp; &nbsp; Company Sales Price per ton&nbsp; (red line)</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUM(IF([Value Type]= 'CompanySales' , [Sales]))</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))</P> <P>c) Monthly Avg - Company Sales Price per Ton&nbsp; (green&nbsp; line)</P> <P>I've tried many different formula approaches.&nbsp; Please help here<BR />The line should find the total Sales for a month (based on Month[Pricing Week]) and divide that by the total Weight/Unit Converter for a month.</P> <P>&nbsp;</P> <P>&nbsp;</P> <TABLE width="1194"> <TBODY> <TR> <TD width="113">Pricing Week&nbsp;</TD> <TD width="92">ID</TD> <TD width="115">Sales</TD> <TD width="127">Weight</TD> <TD width="101">Unit</TD> <TD width="128">Unit Converter</TD> <TD width="135">Pricing Month</TD> <TD width="116">Pricing Year</TD> <TD width="150">Value Type</TD> <TD width="117">Wood Type</TD> </TR> <TR> <TD>6/2/2023</TD> <TD>1</TD> <TD>300</TD> <TD>6</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>6/9/2023</TD> <TD>2</TD> <TD>637</TD> <TD>12</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>7/7/2023</TD> <TD>3</TD> <TD>600</TD> <TD>10</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Cherry</TD> </TR> <TR> <TD>7/14/2023</TD> <TD>4</TD> <TD>355</TD> <TD>6</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/21/2023</TD> <TD>5</TD> <TD>325</TD> <TD>8</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>8/4/2023</TD> <TD>6</TD> <TD>220</TD> <TD>4</TD> <TD>TONS</TD> <TD>1</TD> <TD>8</TD> <TD>2023</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>5/31/2024</TD> <TD>7</TD> <TD>300</TD> <TD>11700</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>5</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Cherry</TD> </TR> <TR> <TD>6/7/2024</TD> <TD>8</TD> <TD>356</TD> <TD>5</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Birch</TD> </TR> <TR> <TD>6/7/2024</TD> <TD>9</TD> <TD>309</TD> <TD>5</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Cherry</TD> </TR> <TR> <TD>6/14/2024</TD> <TD>10</TD> <TD>380</TD> <TD>15100</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>6/14/2024</TD> <TD>11</TD> <TD>290</TD> <TD>6</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>6/21/2024</TD> <TD>12</TD> <TD>311</TD> <TD>5</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>6/28/2024</TD> <TD>13</TD> <TD>287</TD> <TD>5.15</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Chery</TD> </TR> <TR> <TD>6/28/2024</TD> <TD>14</TD> <TD>302</TD> <TD>10500</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>6/28/2024</TD> <TD>15</TD> <TD>250</TD> <TD>5</TD> <TD>TONS</TD> <TD>1</TD> <TD>6</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/5/2024</TD> <TD>16</TD> <TD>240</TD> <TD>5</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/5/2024</TD> <TD>17</TD> <TD>310</TD> <TD>6</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/5/2024</TD> <TD>18</TD> <TD>300</TD> <TD>12000</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/12/2024</TD> <TD>19</TD> <TD>295</TD> <TD>13000</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Birch</TD> </TR> <TR> <TD>7/12/2024</TD> <TD>20</TD> <TD>306</TD> <TD>6</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>7/19/2024</TD> <TD>21</TD> <TD>219</TD> <TD>4</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>7/26/2024</TD> <TD>22</TD> <TD>500</TD> <TD>8</TD> <TD>TONS</TD> <TD>1</TD> <TD>7</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Oak</TD> </TR> <TR> <TD>8/2/2024</TD> <TD>23</TD> <TD>350</TD> <TD>15000</TD> <TD>POUNDS</TD> <TD>2000</TD> <TD>8</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Maple</TD> </TR> <TR> <TD>8/2/2024</TD> <TD>24</TD> <TD>308</TD> <TD>7</TD> <TD>TONS</TD> <TD>1</TD> <TD>8</TD> <TD>2024</TD> <TD>CompanySales</TD> <TD>Cherry</TD> </TR> <TR> <TD>5/31/2024</TD> <TD>25</TD> <TD>3.93</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>5</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>6/7/2024</TD> <TD>26</TD> <TD>4.22</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>6/14/2024</TD> <TD>27</TD> <TD>4.67</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>6/21/2024</TD> <TD>28</TD> <TD>5.11</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>6/28/2024</TD> <TD>29</TD> <TD>4.82</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>7/5/2024</TD> <TD>30</TD> <TD>4.51</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>7</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>7/12/2024</TD> <TD>31</TD> <TD>4.42</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>7</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>7/19/2024</TD> <TD>32</TD> <TD>4.18</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>7</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>7/26/2024</TD> <TD>33</TD> <TD>3.87</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>7</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>8/2/2024</TD> <TD>34</TD> <TD>4.05</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>8</TD> <TD>2024</TD> <TD>Index</TD> <TD>&nbsp;</TD> </TR> </TBODY> </TABLE> Wed, 14 Aug 2024 22:57:27 GMT https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476389#M100272 SheilaBI 2024-08-14T22:57:27Z Re: Chart Monthly Average over Weekly data https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476430#M100277 <P>Take a look at the aggr() function to solve this.</P> <P>&nbsp;</P> <P>In the screenshot below I 've loaded your data and applied the following two measures to the line chart.&nbsp;</P> <P><STRONG>Company Sales Price per ton&nbsp;<BR /></STRONG><FONT face="courier new,courier" size="2">SUM(IF([Value Type]= 'CompanySales' , [Sales]))<BR /></FONT><FONT face="courier new,courier" size="2">/<BR />SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))</FONT></P> <P><STRONG>Pricing Month avg Company Sales Price per ton<BR /></STRONG><FONT face="courier new,courier" size="2">aggr(NODISTINCT <BR />&nbsp; SUM(IF([Value Type]= 'CompanySales' , [Sales]))</FONT><BR /><FONT face="courier new,courier" size="2">&nbsp; / <BR />&nbsp; SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))</FONT><BR /><FONT face="courier new,courier" size="2">, [Pricing Year],[Pricing Month])</FONT></P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Vegar_1-1723707410024.png" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/170660iF0D4F08A755343F1/image-size/large?v=v2&amp;px=999" role="button" title="Vegar_1-1723707410024.png" alt="Vegar_1-1723707410024.png" /></span></P> <P>I hope this will help you getting the visualisation that you need.</P> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 15 Aug 2024 07:39:53 GMT https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476430#M100277 Vegar 2024-08-15T07:39:53Z Re: Chart Monthly Average over Weekly data https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476523#M100287 <P>Vegar!&nbsp;Thank you!!<BR />I was so close in the formula I built and your answer helped me to tweak and get to the visualization I needed.<BR /><BR />I hope this solution helps someone else as well!<BR /><BR />FYI, I used this explanation of the AGGR functions NODISTINCT attribute:<BR /><A href="https://community.qlik.com/t5/QlikView-App-Dev/What-NODISTINCT-parameter-does-in-AGGR-function/td-p/368153" target="_blank">https://community.qlik.com/t5/QlikView-App-Dev/What-NODISTINCT-parameter-does-in-AGGR-function/td-p/368153</A></P> Thu, 15 Aug 2024 16:40:02 GMT https://community.qlik.com/t5/App-Development/Chart-Monthly-Average-over-Weekly-data/m-p/2476523#M100287 SheilaBI 2024-08-15T16:40:02Z