topic Need to Calculate Difference of Date/Times of Same Column in Dashboards & Analytics Discussions https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3842#M327 <P>I have data in the below format:<BR /><BR /></P><TABLE width="737"><TBODY><TR><TD width="98">ResourceName</TD><TD width="107">TxnDate</TD><TD width="88">TxnTypeName</TD><TD width="111">ContainerName</TD><TD width="111">Qty</TD><TD width="111">SpecName</TD><TD width="111">ProductName</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:41 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_SU_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:42 PM</TD><TD>MoveStd</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_SU_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:42 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_FA_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:43 PM</TD><TD>MoveStd</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_FA_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:43 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_TU_SP</TD><TD>00346-20016A</TD></TR></TBODY></TABLE><P>Please note:&nbsp; in the below I don't want to aggregate the DateTime field - saying Max was the only way I could get it to display correctly.</P><P>I need to pivot the Transaction Types to take the difference of the times from MoveIn to MoveStd:</P><TABLE width="958"><TBODY><TR><TD width="115">&nbsp;</TD><TD width="115">&nbsp;</TD><TD width="113">&nbsp;</TD><TD width="88">&nbsp;</TD><TD width="111">Values</TD><TD width="111">TxnTypeName</TD><TD width="111">&nbsp;</TD><TD width="111">&nbsp;</TD><TD width="83">&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Sum of Qty</TD><TD>&nbsp;</TD><TD>Max of TxnDate</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>ResourceName</TD><TD>ContainerName</TD><TD>ProductName</TD><TD>SpecName</TD><TD>MoveIn</TD><TD>MoveStd</TD><TD>MoveIn</TD><TD>MoveStd</TD><TD>TimeDiff</TD></TR><TR><TD>SR10-48</TD><TD>AG6826626</TD><TD>00346-20016A</TD><TD>F83_FA_SP</TD><TD>600</TD><TD>600</TD><TD>1/23/23 3:42 PM</TD><TD>1/23/23 3:43 PM</TD><TD>0:00:33</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>F83_SU_SP</TD><TD>600</TD><TD>600</TD><TD>1/23/23 3:41 PM</TD><TD>1/23/23 3:42 PM</TD><TD>0:01:10</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>F83_TU_SP</TD><TD>600</TD><TD>&nbsp;</TD><TD>1/23/23 3:43 PM</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>How could I do this in Incorta?<BR />Excel file also attached.</P> Wed, 01 Mar 2023 19:56:04 GMT olekern 2023-03-01T19:56:04Z Need to Calculate Difference of Date/Times of Same Column https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3842#M327 <P>I have data in the below format:<BR /><BR /></P><TABLE width="737"><TBODY><TR><TD width="98">ResourceName</TD><TD width="107">TxnDate</TD><TD width="88">TxnTypeName</TD><TD width="111">ContainerName</TD><TD width="111">Qty</TD><TD width="111">SpecName</TD><TD width="111">ProductName</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:41 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_SU_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:42 PM</TD><TD>MoveStd</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_SU_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:42 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_FA_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:43 PM</TD><TD>MoveStd</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_FA_SP</TD><TD>00346-20016A</TD></TR><TR><TD>SR10-48</TD><TD>1/23/23 3:43 PM</TD><TD>MoveIn</TD><TD>AG6826626</TD><TD>600</TD><TD>F83_TU_SP</TD><TD>00346-20016A</TD></TR></TBODY></TABLE><P>Please note:&nbsp; in the below I don't want to aggregate the DateTime field - saying Max was the only way I could get it to display correctly.</P><P>I need to pivot the Transaction Types to take the difference of the times from MoveIn to MoveStd:</P><TABLE width="958"><TBODY><TR><TD width="115">&nbsp;</TD><TD width="115">&nbsp;</TD><TD width="113">&nbsp;</TD><TD width="88">&nbsp;</TD><TD width="111">Values</TD><TD width="111">TxnTypeName</TD><TD width="111">&nbsp;</TD><TD width="111">&nbsp;</TD><TD width="83">&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Sum of Qty</TD><TD>&nbsp;</TD><TD>Max of TxnDate</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>ResourceName</TD><TD>ContainerName</TD><TD>ProductName</TD><TD>SpecName</TD><TD>MoveIn</TD><TD>MoveStd</TD><TD>MoveIn</TD><TD>MoveStd</TD><TD>TimeDiff</TD></TR><TR><TD>SR10-48</TD><TD>AG6826626</TD><TD>00346-20016A</TD><TD>F83_FA_SP</TD><TD>600</TD><TD>600</TD><TD>1/23/23 3:42 PM</TD><TD>1/23/23 3:43 PM</TD><TD>0:00:33</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>F83_SU_SP</TD><TD>600</TD><TD>600</TD><TD>1/23/23 3:41 PM</TD><TD>1/23/23 3:42 PM</TD><TD>0:01:10</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>F83_TU_SP</TD><TD>600</TD><TD>&nbsp;</TD><TD>1/23/23 3:43 PM</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>How could I do this in Incorta?<BR />Excel file also attached.</P> Wed, 01 Mar 2023 19:56:04 GMT https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3842#M327 olekern 2023-03-01T19:56:04Z Re: Need to Calculate Difference of Date/Times of Same Column https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3845#M328 <P>Check out this post - seems applicable here:&nbsp;</P><P>&nbsp;<A href="https://community.incorta.com/t5/data-schema-discussions/can-we-use-analytic-functions-like-lead-or-lag-in-incorta/m-p/2646#M165" target="_blank">https://community.incorta.com/t5/data-schema-discussions/can-we-use-analytic-functions-like-lead-or-lag-in-incorta/m-p/2646#M165</A></P> Wed, 01 Mar 2023 21:38:14 GMT https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3845#M328 RADSr 2023-03-01T21:38:14Z Re: Need to Calculate Difference of Date/Times of Same Column https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3854#M331 <P>I would recommend using a Pivot function in an MV for this as illustrated in the thread linked. It should be fairly easy for you to do if you read through it, but let us know if you need any further guidance and I would be happy to help further.&nbsp;<A href="https://community.incorta.com/t5/dashboards-analytics-discussions/what-is-a-simple-excel-reference-is-hard-in-incorta/m-p/3168#M249" target="_blank">https://community.incorta.com/t5/dashboards-analytics-discussions/what-is-a-simple-excel-reference-is-hard-in-incorta/m-p/3168#M249</A></P> Fri, 03 Mar 2023 15:52:30 GMT https://community.incorta.com/t5/dashboards-analytics-discussions/need-to-calculate-difference-of-date-times-of-same-column/m-p/3854#M331 ZachBreimayer 2023-03-03T15:52:30Z