6 Replies Latest reply on Jul 24, 2012 11:38 AM by hub.yoo

    Creating a calculation based on two different data sources

    hub.yoo

      Hello,

       

      I am trying to use elements from two different data sources in a calculation.

       

      In one database I have the current open orders for the company. In another database, I have inventory information, ie qty in stock and warehouse location. This second data source is pretty important because i need to use the warehouse data information to determine if a open order is due to have 0 qty in main stock which would put the open order into a "no stock" type of back order.

       

      I saw in a different posting that i need to make everything an attribute to use elements from different data sources.

       

      I'm sorry for the convoluted calculation but my boss wants me to use all the "rules" that the person creating the open order report is using...

       

      My original calculation worked but didnt incorporate the warehouse information so some elements were being miscategorized.

       

      IF [CSStatus] = "HOLD" OR contains([AcctStatus],"HOLD") then "HOLD"
      ELSEIF ([Country_Name] = "ARGENTINA" OR [Country_Name] = "PORTUGAL" OR [Country_Name] = "LITHUANIA" OR [Country_Name] = "BRAZIL" OR [Country_Name] = "LATVIA") THEN "HOLD"
      ELSEIF SalesPerson = "Klein" AND [Qty_Ordered] >= 20 then "HOLD"
      ELSEIF contains(ShippingStatus, "Do Not Ship") then "HOLD"

      elseif Qty = 0 and Qty_Log = 0 then "No Stock"

      elseif Qty > 0 and Qty_Log = 0 then "Available to Ship"

      elseif Qty_Log > 0 then "Partial N-Logged"
      End

       

      My new calculation making everything an Attr causes 96% of the elements to fall under the "null" category..

       

      IF attr([CSStatus]) = "HOLD" OR contains(attr([AcctStatus]),"HOLD") then "HOLD"
      ELSEIF (attr([Country_Name]) = "ARGENTINA" OR attr([Country_Name]) = "PORTUGAL"
      OR attr([Country_Name]) = "LITHUANIA" OR attr([Country_Name]) = "BRAZIL" OR attr([Country_Name]) = "LATVIA") THEN "HOLD"
      ELSEIF attr(SalesPerson) = "Klein" AND attr([Qty_Ordered]) >= 20 then "HOLD"
      ELSEIF contains(attr(ShippingStatus), "Do Not Ship") then "HOLD"

      elseif  ATTR([InventoryByPartNo (Inventory)].[MSS]) = "Main 0" and attr(Qty_Log) = 0 then "No Stock"

      elseif attr(Qty) > 0 and attr(Qty_Log) = 0 then "Available to Ship"

      elseif attr(Qty_Log) > 0 then "Partial N-Logged"
      End

       

       

      Am I incorrectly applying the ATTR field? It doesnt let me localize the ATTR to only the secondary datasource.

       

      Would it be better if I break the calculation into component pieces and then create a calculation based on the sub-level calculations?

        • 1. Re: Creating a calculation based on two different data sources
          Tracy Rodgers

          Hi Hub,

           

          Are you able to post a sample workbook (twbx file) so that I can take a closer look?

           

          One option may be to use a different aggregation than attr. Perhaps try Max?

           

          -Tracy

          • 2. Re: Creating a calculation based on two different data sources
            hub.yoo

            Hi Tracy,

             

            I am not exactly sure which fields would be better off max or attr. I tried changing some of the aggregations and when i change any of the qty's to max, it is always a non 0 number so it dominates the categories...

            • 3. Re: Creating a calculation based on two different data sources
              Tracy Rodgers

              Hi Hub,

               

              I've changed the calculation to the following, and it seems to working as it's supposed to--however, I may be missing something. Let me know if it doesn't work and where it's going wrong!

               

              IF attr([CSStatus]) = "HOLD" OR contains(attr([AcctStatus]),"HOLD") then "HOLD"

              ELSEIF (attr([Country_Name]) = "ARGENTINA" OR attr([Country_Name]) = "PORTUGAL"

              OR attr([Country_Name]) = "LITHUANIA" OR attr([Country_Name]) = "BRAZIL" OR attr([Country_Name]) = "LATVIA") THEN "HOLD"

              ELSEIF attr(SalesPerson) = "Klein" AND sum([Qty_Ordered]) >= 20 then "HOLD"

              ELSEIF contains(attr(ShippingStatus), "Do Not Ship") then "HOLD"

               

              elseif  ATTR([InventoryByPartNo (Inventory)].[Warehouse]) = "Main" and

              SUM([InventoryByPartNo (Inventory)].[Qty]) = 0 and ATTR([InventoryByPartNo (Inventory)].[AsOfDate]) = Attr(today() -1)

              and sum(Qty_Log) = 0 then "No Stock"

               

              elseif sum(Qty) > 0 and sum(Qty_Log) = 0 then "Available to Ship"

               

              elseif attr(Qty_Log) > 0 then "Partial N-Logged"

              End

               

              -Tracy

              • 4. Re: Creating a calculation based on two different data sources
                hub.yoo

                Hi Tracy, wow that was a very quick reply!

                 

                Unfortunately on my computer the calculations all yield null values...

                 

                 

                nulls.JPG

                • 5. Re: Creating a calculation based on two different data sources
                  Tracy Rodgers

                  Hi Hub,

                   

                  The reason that the calculation is returning null is because the calculation is dependent on several factors that are not in the view. (One note--change the last line of the calculation to be sum(Qty_Log)--this will at least be recognized as Partial N-Logged). By placing Country_Name and the other fields in the calc on the level of detail shelf, more colors will start to appear. However, adding these fields will break up the color in your bar chart quite a bit. To see that the calculation is working as expected--create a crosstab placing all of these fields on the view with the calculation.

                   

                  If possible I would explore other chart types or see if the calculation can be any more concise.

                   

                  Hope this at least gives some insight!

                   

                  -Tracy

                  1 of 1 people found this helpful
                  • 6. Re: Creating a calculation based on two different data sources
                    hub.yoo

                    nulls reduced.jpg

                    I got closer to what i need by adding the order_No and item ID to the level of detail. however it isnt grouped by bko and No_Stock = null...

                     

                    sorry, the tableau didnt have the order_No field before. added the updated workbook for anyone interested.