topic Re: data subtraction calculation methos in QlikView https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12919#M789997 <HTML><HEAD></HEAD><BODY><P>Thank it worked for me. </P></BODY></HTML> Sat, 17 Feb 2018 15:07:32 GMT bushpalaamarnat 2018-02-17T15:07:32Z data subtraction calculation methos https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12917#M789995 <HTML><HEAD></HEAD><BODY><P>Hi all, </P><P></P><P>Could any one please help. </P><P></P><P><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: 12.8px;">Out put required is (week &amp; material &amp; Loc) week 6 - DFC Days should be subtracted from&nbsp; </SPAN><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;">(week &amp; material &amp; Loc) week 7&nbsp; - DFC Days. the out put of this subtraction is in the new column. This way it should subtracted from week 8 - week 7, week 9- week 8 for 52 weeks. </SPAN></P><P><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;"><BR /></SPAN></P><P><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;">Data Examples of input </SPAN></P><P><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;"><BR /></SPAN></P><P>&nbsp;&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" height="424" style="width: 744px; height: 318px;" width="742"><TBODY><TR><TD class="xl63" colspan="4" height="20" width="414">Input</TD></TR><TR><TD height="20">Week</TD><TD>Material</TD><TD>Loc</TD><TD>DFC Days</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">1234</TD><TD>USD</TD><TD align="right">4</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">2345</TD><TD>IND</TD><TD align="right">230</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">3456</TD><TD>FR</TD><TD align="right">450</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">1234</TD><TD>UK</TD><TD align="right">36</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">9568</TD><TD>UK</TD><TD align="right">41</TD></TR><TR><TD height="20">W07-18</TD><TD align="right">1234</TD><TD>USD</TD><TD align="right">9</TD></TR><TR><TD height="20">W07-18</TD><TD align="right">2345</TD><TD>IND</TD><TD align="right">237</TD></TR><TR><TD height="20">W07-18</TD><TD align="right">9568</TD><TD>UK</TD><TD align="right">46</TD></TR><TR><TD height="20">W06-18</TD><TD align="right">3456</TD><TD>FR</TD><TD align="right">455</TD></TR><TR><TD height="20">W07-18</TD><TD align="right">1234</TD><TD>UK</TD><TD align="right">41</TD></TR><TR><TD height="20">W08-18</TD><TD align="right">9568</TD><TD>UK</TD><TD align="right">51</TD></TR><TR><TD height="20">W08-18</TD><TD align="right">1234</TD><TD>USD</TD><TD align="right">14</TD></TR><TR><TD height="20">W08-18</TD><TD align="right">2345</TD><TD>IND</TD><TD align="right">244</TD></TR><TR><TD height="20">W08-18</TD><TD align="right">3456</TD><TD>FR</TD><TD align="right">460</TD></TR><TR><TD height="20">W08-18</TD><TD align="right">1234</TD><TD>UK</TD><TD align="right">46</TD></TR></TBODY></TABLE><P></P><P></P><P>Out put required is as follows </P><P></P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><TABLE align="left" border="0" cellpadding="0" cellspacing="0" jive-data-cell="{&quot;color&quot;:&quot;#575757&quot;,&quot;textAlign&quot;:&quot;left&quot;,&quot;padding&quot;:&quot;NaN&quot;,&quot;backgroundColor&quot;:&quot;transparent&quot;,&quot;fontFamily&quot;:&quot;arial,helvetica,sans-serif&quot;,&quot;verticalAlign&quot;:&quot;baseline&quot;}" jive-data-header="{&quot;color&quot;:&quot;#FFFFFF&quot;,&quot;backgroundColor&quot;:&quot;#6690BC&quot;,&quot;textAlign&quot;:&quot;center&quot;,&quot;padding&quot;:&quot;2&quot;,&quot;fontFamily&quot;:&quot;arial,helvetica,sans-serif&quot;,&quot;verticalAlign&quot;:&quot;baseline&quot;}" style="width: 783px;"><TBODY><TR><TH style="color: #ffffff; background-color: #6690bc; text-align: center; padding: 2px;"></TH><TH style="color: #ffffff; background-color: #6690bc; text-align: center; padding: 2px;">Output</TH></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">Week</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">Material</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">Loc</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">DFC Days</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">Difference in Days</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">USD</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">4</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">2345</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">IND</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">230</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">7</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">3456</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">FR</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">450</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">36</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">9568</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">41</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W07-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">USD</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">9</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W07-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">2345</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">IND</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">237</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">7</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W07-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">9568</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">46</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W06-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">3456</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">FR</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">455</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W07-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">41</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">5</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W08-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">9568</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">51</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">-51</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W08-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">USD</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">14</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">-14</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W08-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">2345</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">IND</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">244</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">-244</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W08-18</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">3456</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">FR</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">460</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">-460</TD></TR><TR><TD height="20" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">W08-18</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">1234</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">UK</TD><TD align="right" style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">46</TD><TD style="color: #575757; text-align: left; background-color: rgba(0, 0, 0, 0); font-family: arial, helvetica, sans-serif; vertical-align: baseline;">-46</TD></TR></TBODY></TABLE></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12917#M789995 bushpalaamarnat 2020-11-25T16:16:04Z Re: data subtraction calculation methos https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12918#M789996 <HTML><HEAD></HEAD><BODY><P>Try this</P><P></P><P><SPAN style="font-size: 13.3333px;"><STRONG>Table:</STRONG></SPAN></P><P><STRONG style="font-size: 13.3333px;">LOAD * INLINE [</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; Week, Material, Loc, DFC Days</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W06-18, 1234, USD, 4</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W06-18, 2345, IND, 230</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W06-18, 3456, FR, 450</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W06-18, 1234, UK, 36</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W06-18, 9568, UK, 41</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W07-18, 1234, USD, 9</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W07-18, 2345, IND, 237</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W07-18, 9568, UK, 46</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W07-18, 3456, FR, 455</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W07-18, 1234, UK, 41</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W08-18, 9568, UK, 51</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W08-18, 1234, USD, 14</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W08-18, 2345, IND, 244</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W08-18, 3456, FR, 460</STRONG></P><P><STRONG style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp; W08-18, 1234, UK, 46</STRONG></P><P><STRONG style="font-size: 13.3333px;">];</STRONG></P><P><STRONG style="font-size: 13.3333px;"><BR /></STRONG></P><P><STRONG style="font-size: 13.3333px;">FinalTable:</STRONG></P><P><STRONG style="font-size: 13.3333px;">LOAD *,</STRONG></P><P><STRONG style="font-size: 13.3333px;"> If(Material = Previous(Material) and Loc = Previous(Loc), Previous([DFC Days]) - [DFC Days], -[DFC Days]) as [Difference in Days]</STRONG></P><P><STRONG style="font-size: 13.3333px;">Resident Table</STRONG></P><P><STRONG style="font-size: 13.3333px;">Order By Material, Loc, Week desc;</STRONG></P><P><STRONG style="font-size: 13.3333px;"><BR /></STRONG></P><P><SPAN style="font-size: 13.3333px;"><STRONG>DROP Table Table;</STRONG></SPAN></P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/193263_Capture.PNG" style="height: auto;" /></P></BODY></HTML> Tue, 13 Feb 2018 12:31:29 GMT https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12918#M789996 sunny_talwar 2018-02-13T12:31:29Z Re: data subtraction calculation methos https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12919#M789997 <HTML><HEAD></HEAD><BODY><P>Thank it worked for me. </P></BODY></HTML> Sat, 17 Feb 2018 15:07:32 GMT https://community.qlik.com/t5/QlikView/data-subtraction-calculation-methos/m-p/12919#M789997 bushpalaamarnat 2018-02-17T15:07:32Z