https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2061903#M87100 <P>Hi,</P> <P>I have two tables:</P> <P><STRONG>table1:</STRONG></P> <P>date_1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; product &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;quantity</P> <P>10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5</P> <P>12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</P> <P>10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</P> <P>11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2</P> <P>&nbsp;</P> <P><STRONG>table 2:</STRONG></P> <P>product&nbsp; &nbsp;STORE&nbsp; date_2&nbsp; &nbsp; price&nbsp; &nbsp; range_from&nbsp; &nbsp; &nbsp;range_to</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11.02&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 01&nbsp; &nbsp; &nbsp;&nbsp;</P> <P>b&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.02&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 01&nbsp; &nbsp; &nbsp;&nbsp;</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</P> <P>In table in QS i want to sum(quantity) but i need condition if date_1 &gt;=date_2 .&nbsp;</P> <P><SPAN>Ultimately I would like to have a table:</SPAN></P> <P><STRONG>Table 3:</STRONG></P> <P><SPAN>product&nbsp; &nbsp; store&nbsp; &nbsp; &nbsp; sum(quantity if date_1 &gt;=date_2)</SPAN></P> <P><SPAN>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; 8</SPAN></P> <P><SPAN>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</SPAN></P> <P><SPAN>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</SPAN></P> <P><SPAN>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2</SPAN></P> <P>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>Does anyone know how to write a condition in set expression?</SPAN></P> <P><SPAN>It does not work:</SPAN></P> <P><SPAN>aggr(nodistinct sum( {&lt;store=, product=&gt;} if(data_1&gt;=date_2, quantity,0)),store,product)<BR /></SPAN></P> Wed, 26 Apr 2023 13:06:04 GMT Modiz 2023-04-26T13:06:04Z https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2061903#M87100 <P>Hi,</P> <P>I have two tables:</P> <P><STRONG>table1:</STRONG></P> <P>date_1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; product &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;quantity</P> <P>10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5</P> <P>12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</P> <P>10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</P> <P>11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2</P> <P>&nbsp;</P> <P><STRONG>table 2:</STRONG></P> <P>product&nbsp; &nbsp;STORE&nbsp; date_2&nbsp; &nbsp; price&nbsp; &nbsp; range_from&nbsp; &nbsp; &nbsp;range_to</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11.02&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 01&nbsp; &nbsp; &nbsp;&nbsp;</P> <P>b&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.02&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 01&nbsp; &nbsp; &nbsp;&nbsp;</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11.02&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</P> <P>In table in QS i want to sum(quantity) but i need condition if date_1 &gt;=date_2 .&nbsp;</P> <P><SPAN>Ultimately I would like to have a table:</SPAN></P> <P><STRONG>Table 3:</STRONG></P> <P><SPAN>product&nbsp; &nbsp; store&nbsp; &nbsp; &nbsp; sum(quantity if date_1 &gt;=date_2)</SPAN></P> <P><SPAN>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; 8</SPAN></P> <P><SPAN>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</SPAN></P> <P><SPAN>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</SPAN></P> <P><SPAN>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2</SPAN></P> <P>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>Does anyone know how to write a condition in set expression?</SPAN></P> <P><SPAN>It does not work:</SPAN></P> <P><SPAN>aggr(nodistinct sum( {&lt;store=, product=&gt;} if(data_1&gt;=date_2, quantity,0)),store,product)<BR /></SPAN></P> Wed, 26 Apr 2023 13:06:04 GMT https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2061903#M87100 Modiz 2023-04-26T13:06:04Z https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2063250#M87209 <P>you are actualyy getting a cartesian just bec you are not linking your two tables.&nbsp; change the column name of Product in table 2 to type and try it again.&nbsp; without any explanation and just looking at your data, this assumes that type and product are the same.</P> Fri, 21 Apr 2023 19:33:11 GMT https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2063250#M87209 edwin 2023-04-21T19:33:11Z https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2063446#M87219 <P>In the brand-new horror game <SPAN><A href="https://poppy-playtime.io" target="_blank">poppy playtime</A></SPAN>, you'll be alone in a huge toy factory while a scary beast chases you. Will you be ready for this trip? Start right away and have fun!</P> Mon, 24 Apr 2023 06:33:55 GMT https://community.qlik.com/t5/App-Development/summing-values-in-a-column-with-a-condition-based-on-two/m-p/2063446#M87219 fnaf12 2023-04-24T06:33:55Z