12 Replies Latest reply on Sep 7, 2018 9:25 AM by Joe Oppelt

    Blending, if function and calculated variables

    Matt Doe

      Hi everyone,

       

      I want to start off by thanking you for giving some time to my issue.

      I am attempting to use blending of 2 data sources to perform a calculation. I wish I could have just one database where I can perform a join between the 2 sources, but this is not possible.

       

       

      The primary data source is composed of shipment, their pickup date, estimated pickup date and the customer for which they were picked up (A/B/C/D). Some dates are blank on one side, the other or both. The secondary data source is composed of complaints related to those shipments along with the causing parties (E/F/G/H). Some shipments may have complaints, some may not and some shipments may have more than one complaint with different causing parties.

       

      First I started calculating the performance based on primary data source alone with regards to timeliness. Then I added a Net/Gross parameter, a blend between the 2 data sources and a slightly different performance calculation.

      The idea behind 'Net/Gross' is that if the shipment is late and causing party is not either G or H they should be considered on time. This would be the net result.

       

      The blend is done on Shipment ID.

       

      At the overall level I had this:

       

      if NOT(ISNULL([Pick Up]))

          and [Pick Up]<TODAY()

          AND [v PickupDayDiff]>0

          then "LATE"

      elseif NOT(ISNULL([Pick Up]))

          and [Pick Up]<TODAY()

          AND [v PickupDayDiff]<=0

          then "ON TIME"

       

      elseif NOT(ISNULL([PickUp Estimate]))

          AND (ISNULL([Pick Up]))

          and [PickUp Estimate]<TODAY()

          then "PICKUP DUE"

       

      ELSE "DATA MISSING"

      END

       

      This is a Dimension. It does not take into account anything regarding causing parties and is only based on primary data source.

       

      After I changed to this:

       

      if NOT(ISNULL(max([Pick Up])))

          and max([Pick Up])<max(TODAY())

          AND max([v PickupDayDiff])>0 then

             if [Result type]='Net' then

                  if (ATTR([Complaints (Compaints)].[Cause])='G' or ATTR([Complaints (Compaints)].[Cause]) ='H')

                  then "LATE"

                  else "ON TIME"

                  end

             else "LATE"

             end

      elseif NOT(ISNULL(max([Pick Up])))

          and max([Pick Up])<TODAY()

          AND max([v PickupDayDiff])<=0

          then "ON TIME"

       

      elseif NOT(ISNULL(max([PickUp Estimate])))

          AND (ISNULL(max([Pick Up])))

          and max([PickUp Estimate])<TODAY()

          then "PICKUP DUE"

       

      ELSE "DATA MISSING"

      END

       

      Now this is a Measure (with no possibility to transform into a Dimension). It uses both primary and secondary data sources.

       

      Below I will post the formula used to determine performance:

       

      Primary:

       

      COUNTD(IF [v StatusPickup]="ON TIME"

       

              THEN [Shipment ID] END)

      /

      COUNTD(IF [v StatusPickup]="ON TIME"

       

              OR [v StatusPickup]="LATE"

                  THEN [Shipment ID] END)

       

      Primary and secondary:

       

      (IF [v StatusPickup 2]="ON TIME"

       

              THEN 1 else 0 END)

      /

      (IF [v StatusPickup 2]="ON TIME"

       

              OR [v StatusPickup 2]="LATE"

                  THEN 1 else 0 END)

       

      I cannot find a way to make the blend function as the way it is when the performance is calculated based only on the primary data source. I am using version 2018.02.

       

      Any ideas are welcomed!

        • 1. Re: Blending, if function and calculated variables
          Joe Oppelt

          I'm looking at this.

           

          I need more explanation.  (I could reverse-engineer your  calcs to try to understand them, but can you explain what you are looking to do in each calc?)

           

          For the record, the vStatus2 calc has to be a measure because it has aggregates in it.  And that's the only way you can bring blended data from a secondary source.

          • 2. Re: Blending, if function and calculated variables
            Joe Oppelt

            Also, I notice that you are using ATTR() to bring back the [Cause] value from the Complaints source.  You are blending on ShipmentID, and for some shipments in the secondary source you have multiple causes.  (See S_10 or S_18, for example.)  ATTR() will return an asterisk for this.  When you have multiples, which cause do you actually want to return?

            • 3. Re: Blending, if function and calculated variables
              Joe Oppelt

              In the attached I made simple sheets for the secondary and the primary sources.  For Sheet 4 (Primary) I have displayed the two vStatus values.  Some rows have different values.  That's what we need to attack.

              • 4. Re: Blending, if function and calculated variables
                Matt Doe

                Thank you for this Joe! I cannot view the example posted by you just yet as I haven't got acces to the software now.

                 

                To give some more detailed explanations, StatusPick variable should determine the outcome of the shipment (On Time, Late, Pickup Due, Missing); the performance calculation determines the performance for each customer based on the numebr of on time/lates.

                 

                If a shipment is late, has multiple causes and any one of them is G or H then it's on time.

                 

                I hope this clears things up a bit.

                 

                Thank you for your help, patience and time

                • 5. Re: Blending, if function and calculated variables
                  Matt Doe

                  Sorry for the typos

                  • 6. Re: Blending, if function and calculated variables
                    Joe Oppelt

                    OK.  So in the first workbook I provided, I showed both vStatus values.

                     

                    In the one attached here I made some changes to bring "G" or "H" back properly (whether or not there are multiple rows in the secondary source.)

                     

                    In the secondary source I made a FIXED LOD to process through the individual rows.  This gets done before the blending happens.

                     

                    The calc sets a value of 1 or 0.  (Value = 1 if there is a G or H.)  the FIXED LOD sums up those 1s and 0s for each Shipment.  So you'll see on Sheet 3 that S_35 has multiple rows, and one has a G and one has an H.  So for that shipment the value is 2.  For others that have a G or H the value is 1.

                     

                    Now in the primary source I changed vStatus 2 to look for that number (which is a dimension in the secondary.)  If >0, we have a G or H (or both).

                     

                    Now, almost all the vStatus values match.  I see two that do not.  S_21 and s_45.  I suspect something else is going amiss in the rest of the calc, and you can sort through that to see why they are coming up as ON TIME in the V2 calcm but LATE in the primary calc.

                     

                    When you get a chance, look at both workbooks to see the changes I just made in this latest one.

                    • 7. Re: Blending, if function and calculated variables
                      Matt Doe

                      Did not know that one needs to create and aggregation in the secondary source in order for this to work. Thank you for showing me this!

                       

                      For S_21 and S_45 is at I would expect it. They have an H cause, then it's transformed in on time. Something I did not expect and need to think is that if the shipment is late and it doesn't have a complaint it still gets switched to on time. That I fixed with an extra check in the v StatusPickup 2. ( if (ATTR([Complaints (Compaints)].[Cause = G or H?]) = 0 or isnull(ATTR([Complaints (Compaints)].[Cause = G or H?]))) )

                       

                      Now going on the 'Want' tab, if I want to drag the 'Pickup performance (%) 2' and mix again primary and secondary data then the error is:

                      Cannot blend the secondary data source because one or more fields use an unsupported aggregation.

                       

                      This happens regardless if I drag it on the rows or on the Marks space as a 'text'. From what I understand, it's because the link variable must be present in the view, or else it does not work. I tried with the solution of

                      IF FIRST()==0

                      then window_sum(...)

                      end

                       

                      but it still doesn't work.

                       

                      Thank you for everything else!

                      • 8. Re: Blending, if function and calculated variables
                        Joe Oppelt

                        Interesting.

                         

                        So the calc I made was working on Sheet 4 but not on "Want" because Sheet 4 is granularized to the ShipmentID level.  "Want" is not.

                         

                        I changed things.  I approached it by making a new calc in the secondary, which is very much like the old one, but not an LOD.  See [Is Cause = G or H?].  I just set a 1 or 0 for each row.  But now I'm bringing it to the primary as a SUM().  For all rows under a given Shipment, add up the 1s and 0s, and use that number.  I changed vStatus2 to use the new calc.

                         

                        It works in both Sheet 4 and "Want".  (I assume the math is correct and you need to see 100% where it's showing that value.)

                         

                        See attached.

                        • 9. Re: Blending, if function and calculated variables
                          Matt Doe

                          Close, but now I know the difference on how to make the link. (using the sum)

                           

                          The idea is that when the parameter is on 'Gross', the sheets 'Have' and 'Want' should have the same percentages. When the parameter is on 'Net' then the expected performance would increase for some customers slightly on the 'Want' sheet.

                           

                          It would be amazing if the 'Performance (%) 2' would use the COUNTD (just like 'Performance (%)' )because it would make the calculation easier, but the data is already aggregated and cannot apply a COUNTD and aggregate it further.

                           

                          Sorry for not explaining this clearly.

                          • 10. Re: Blending, if function and calculated variables
                            Joe Oppelt

                            Regarding Gross and Net, I only see one place where that comes into play.  In vStatus2 I see this nested IF logic:

                             

                                   if [Result type]='Net' then

                                        if sum([Complaints (Compaints)].[Is Cause = G or H?]) = 0

                                        then "LATE"

                                        else "ON TIME"

                                        end

                                   else "LATE"

                                   end

                             

                            You'll have to debug how/why you're getting the results you are seeing with respect to that parameter.

                             

                            And I'm not sure what you're looking for regarding the COUNTD in vStatus2.  You're right.  You can't use it there because of existing aggregates.  So it takes some involved logic to simulate it when aggregates are already present.  Are you getting the counts you expect?

                             

                            (Keep in mind that I am not immersed in the data and business requirements here like you are.  I just helped out sorting through the intricacies of getting the blended data delivered to your primary source.  The way you want to debug this is to make a bogus sheet like I did for Sheet 3 and Sheet 4 to see what the data looks like and figure out how to make it behave the way you want.  Break out pieces of the calcs into separate temporary calcs to make sure you are getting the values you expect.)

                            • 11. Re: Blending, if function and calculated variables
                              Matt Doe

                              The counts are fine in sheet 4 and they work as intended. As does the parameter: Net -> some LATE values switch to ON TIME; Gross -> they stay LATE.

                               

                              Now the challenge is to use that information and do a calculation of ON TIME / (LATE + ON TIME) for each customer.

                              • 12. Re: Blending, if function and calculated variables
                                Joe Oppelt

                                In the attached I made a copy of "Want"  See Want(2).


                                Here I created a calc to count up the "ON TIME" rows for each Customer.  You will have to put ShipmentID on the sheet because this is calculated at the Shipment level.  I get 10 "ON TIME" for Customer A.  (Note the table calc settings for [On Time?].)


                                I also pit a calc called [index] and set it so it resets for each Customer.

                                 

                                On Want(3) I moved the index calc to filters and selected for index=1.

                                 

                                That's how you'll handle the fact that you have multiple shipments on the sheet per customer.

                                 

                                Make a similar calc for [Late?].  Then you can do your math.

                                1 of 1 people found this helpful