3 Replies Latest reply on Dec 9, 2016 6:35 AM by Dhanashree Arole

    Data Linkage

    Arihant Jain

      Hello,

       

      I have combined 3 data set in one excel file and data type column gives me the name of different data sets.Different type of data are Requisition, Purchase Order and Invoice.

      Purchase order has requisition # as column filed and invoice has requisition and Purchase Order.

       

      Based on Puchase order status, I have created a formula to determine whether the PO is Open or closed. I want to link, if the PO is open or closed, then all the Requisition and Invoice # along with amount should be associated with the PO status.

       

      I am able to get the requisition number but not the amount and not able to get the Invoice #. I have attached the sample WB along with excel file.

       

      Thanks for the help.

      Arihant Jain

        • 1. Re: Data Linkage
          Dhanashree Arole

          Hi,

           

          I peeked into your data and it seems like missing data may be resulting in the issue. PO Line Status is used as baseline for calculated fields, which is not always populated.

           

          Can you pinpoint an example record that you would like to surface in the dashboard depending on the selected parameters? It can be traced back to data. I can then reverse engineer what might be going wrong.

           

          Thanks,

          Dhanashree

          • 2. Re: Data Linkage
            Arihant Jain

            Hi Dhanashree,

             

            Thanks for taking a look at the problem. Below is the example of one of the PO #.

             

            PO # (column M) - 1000001034 is associated with Req #  1726 (Column B) with amount (Column H) - 3900000. I am able to get the Req # in Column B but not the amount associated with it in my Dashboard.

             

            Same PO is linked with Invoice # (Column T) 6001714 with amount (Column Y) - 1000981.47. I am not able to get the information that invoice # and its amount in the dashboard.

             

            Thanks for the help. Let me know if you have any question.

             

            Regards,

            Arihant Jain

            • 3. Re: Data Linkage
              Dhanashree Arole

              Hey Arihant,

               

              Your logic for calculated field Open/Closed Po is as follows:

               

              IF  [PO Line Status] = "Cancelled" THEN "Closed PO"

              ELSEIF   [PO Line Status]!= "Closed"  THEN "Open PO"

              ELSE "Closed PO"

              END

               

              However, when I reviewed the data in excel file, PO Line Status takes following unique values:

               

              created, partially_received, received

               

              So invariably, you are always assigning "Closed PO" to all records as your calculated feld Open/Closed Po hence the Invoice # does not show up.

               

              Try changing the logic to following:

               

              IF  TRIM([PO Line Status]) = "Cancelled" THEN "Closed PO"

              ELSEIF   TRIM([PO Line Status]) = "partially_received"  OR TRIM([PO Line Status]) = "created" OR TRIM([PO Line Status]) = "received" THEN "Open PO"

              //ELSEIF   [PO Line Status]!= "Closed"  THEN "Open PO"

              ELSE "Closed PO"

              END

               

              Also if you closely look at the data, the invoice# is null most of the times:

               

               

              These are the only records that are retrieved for Open PO.

               

              Hope that clarifies some air. Let me know if you have more questions.

               

              Thanks,

              Dhanashree