topic Conditional sum? in App Development https://community.qlik.com/t5/App-Development/Conditional-sum/m-p/1762241#M58961 <P>What I have:</P><P>Promotion table:</P><TABLE><TBODY><TR><TD><P>Key_promotion</P></TD><TD><P>Start_promotion</P></TD><TD><P>End_promotion</P></TD><TD><P>EAN_code</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>A</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>B</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>C</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>D</P></TD></TR><TR><TD><P>Promotion 2</P></TD><TD><P>9-11-2020</P></TD><TD><P>15-11-2020</P></TD><TD><P>E</P></TD></TR><TR><TD><P>Promotion 2</P></TD><TD><P>9-11-2020</P></TD><TD><P>15-11-2020</P></TD><TD><P>F</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Sales</P><TABLE><TBODY><TR><TD><P>EAN_code</P></TD><TD><P>Key_promotion</P></TD><TD><P>Date</P></TD><TD><P>Amount</P></TD></TR><TR><TD><P>A</P></TD><TD><P>-</P></TD><TD><P>26-10-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>A</P></TD><TD><P>-</P></TD><TD><P>27-10-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>B</P></TD><TD><P>-</P></TD><TD><P>28-10-2020</P></TD><TD><P>6</P></TD></TR><TR><TD><P>B</P></TD><TD><P>-</P></TD><TD><P>29-10-2020</P></TD><TD><P>3</P></TD></TR><TR><TD><P>C</P></TD><TD><P>-</P></TD><TD><P>30-10-2020</P></TD><TD><P>1</P></TD></TR><TR><TD><P>D</P></TD><TD><P>-</P></TD><TD><P>30-10-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>A</P></TD><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>1</P></TD></TR><TR><TD><P>A</P></TD><TD><P>Promotion 1</P></TD><TD><P>3-11-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>B</P></TD><TD><P>Promotion 1</P></TD><TD><P>3-11-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>B</P></TD><TD><P>Promotion 1</P></TD><TD><P>5-11-2020</P></TD><TD><P>7</P></TD></TR><TR><TD><P>C</P></TD><TD><P>Promotion 1</P></TD><TD><P>6-11-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>D</P></TD><TD><P>Promotion 1</P></TD><TD><P>7-11-2020</P></TD><TD><P>12</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The problem I am facing is that when you select a Key_promotion I want to see the sales amount of the promotion <STRONG>while the promotion was active</STRONG>. What I also want is the sales amount before the promotion was active.</P><P>So if I select Promotion 1 I would like to get the table on my dashboard as:</P><TABLE><TBODY><TR><TD><P>Sales week 45</P></TD><TD><P>Sales week 46</P></TD></TR><TR><TD><P>20</P></TD><TD><P>28</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Can someone help me out?</P><P>&nbsp;</P> Tue, 17 Nov 2020 12:09:22 GMT Rvz 2020-11-17T12:09:22Z Conditional sum? https://community.qlik.com/t5/App-Development/Conditional-sum/m-p/1762241#M58961 <P>What I have:</P><P>Promotion table:</P><TABLE><TBODY><TR><TD><P>Key_promotion</P></TD><TD><P>Start_promotion</P></TD><TD><P>End_promotion</P></TD><TD><P>EAN_code</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>A</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>B</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>C</P></TD></TR><TR><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>8-11-2020</P></TD><TD><P>D</P></TD></TR><TR><TD><P>Promotion 2</P></TD><TD><P>9-11-2020</P></TD><TD><P>15-11-2020</P></TD><TD><P>E</P></TD></TR><TR><TD><P>Promotion 2</P></TD><TD><P>9-11-2020</P></TD><TD><P>15-11-2020</P></TD><TD><P>F</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Sales</P><TABLE><TBODY><TR><TD><P>EAN_code</P></TD><TD><P>Key_promotion</P></TD><TD><P>Date</P></TD><TD><P>Amount</P></TD></TR><TR><TD><P>A</P></TD><TD><P>-</P></TD><TD><P>26-10-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>A</P></TD><TD><P>-</P></TD><TD><P>27-10-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>B</P></TD><TD><P>-</P></TD><TD><P>28-10-2020</P></TD><TD><P>6</P></TD></TR><TR><TD><P>B</P></TD><TD><P>-</P></TD><TD><P>29-10-2020</P></TD><TD><P>3</P></TD></TR><TR><TD><P>C</P></TD><TD><P>-</P></TD><TD><P>30-10-2020</P></TD><TD><P>1</P></TD></TR><TR><TD><P>D</P></TD><TD><P>-</P></TD><TD><P>30-10-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>A</P></TD><TD><P>Promotion 1</P></TD><TD><P>2-11-2020</P></TD><TD><P>1</P></TD></TR><TR><TD><P>A</P></TD><TD><P>Promotion 1</P></TD><TD><P>3-11-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>B</P></TD><TD><P>Promotion 1</P></TD><TD><P>3-11-2020</P></TD><TD><P>4</P></TD></TR><TR><TD><P>B</P></TD><TD><P>Promotion 1</P></TD><TD><P>5-11-2020</P></TD><TD><P>7</P></TD></TR><TR><TD><P>C</P></TD><TD><P>Promotion 1</P></TD><TD><P>6-11-2020</P></TD><TD><P>2</P></TD></TR><TR><TD><P>D</P></TD><TD><P>Promotion 1</P></TD><TD><P>7-11-2020</P></TD><TD><P>12</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The problem I am facing is that when you select a Key_promotion I want to see the sales amount of the promotion <STRONG>while the promotion was active</STRONG>. What I also want is the sales amount before the promotion was active.</P><P>So if I select Promotion 1 I would like to get the table on my dashboard as:</P><TABLE><TBODY><TR><TD><P>Sales week 45</P></TD><TD><P>Sales week 46</P></TD></TR><TR><TD><P>20</P></TD><TD><P>28</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Can someone help me out?</P><P>&nbsp;</P> Tue, 17 Nov 2020 12:09:22 GMT https://community.qlik.com/t5/App-Development/Conditional-sum/m-p/1762241#M58961 Rvz 2020-11-17T12:09:22Z Re: Conditional sum? https://community.qlik.com/t5/App-Development/Conditional-sum/m-p/1762270#M58967 <P>since the promotion does not change for each sale, you can join it in the script.&nbsp; i added a promoID and a salesID to allow for testing and the promoID to indicate which promotion applies:<BR /><BR /></P><LI-CODE lang="markup">promotions: load *, rowno() as PromoID inline [ Key_promotion,Start_promotion,End_promotion,EAN_code Promotion 1,2/11/2020,8/11/2020,A Promotion 1,2/11/2020,8/11/2020,B Promotion 1,2/11/2020,8/11/2020,C Promotion 1,2/11/2020,8/11/2020,D Promotion 2,9/11/2020,15/11/2020,E Promotion 2,9/11/2020,15/11/2020,F ]; Sales: load *, RowNo() as SalesID inline [ EAN_code1,Key_promotion1,Date,Amount A,,26/10/2020,2 A,,27/10/2020,4 B,,28/10/2020,6 B,,29/10/2020,3 C,,30/10/2020,1 D,,30/10/2020,4 A,Promotion 1,2/11/2020,1 A,Promotion 1,3/11/2020,2 B,Promotion 1,3/11/2020,4 B,Promotion 1,5/11/2020,7 C,Promotion 1,6/11/2020,2 D,Promotion 1,7/11/2020,12 ]; left join (Sales) load PromoID, EAN_code as EAN_code1,Key_promotion as Key_promotion1, Start_promotion as Start_promotion1,End_promotion as End_promotion1 resident promotions; NoConcatenate NewSales: load SalesID, PromoID, Date, Amount Resident Sales where Date&gt;=Start_promotion1 and Date &lt;=End_promotion1 or isnull(PromoID) and not isnull(SalesID); drop table Sales;</LI-CODE> Tue, 17 Nov 2020 14:05:05 GMT https://community.qlik.com/t5/App-Development/Conditional-sum/m-p/1762270#M58967 edwin 2020-11-17T14:05:05Z