3 Replies Latest reply on Aug 24, 2018 5:58 AM by Zhouyi Zhang

    How to deal with Aggregate calculated dimention

    Xu Han

      Hi Guys,

      Would really appreciate your help. I have below data, "order key" contain multiple "lines", and "order key & line" as the unique ID. Status code 0="allocated", 5="picked", however if one line has two codes, that means this line is "picking in progress". I am having big trouble with those lines with two status codes, I do not know how to properly assign "pick in progress" status to them and count the lines by status.

          

      Order KeyLine NumberOrder Key & LinePick StatusOrder Status
      A001A0010Pick in Progress
      A001A0015Pick in Progress
      A002A0025Picked
      A003A0030Allocated
      B001B0015Picked

       

      I manage to assign the status to each individual line by defining any lines with 2 status are "Pick in progress". I have to force everything to aggregated using ATTR due to COUNT function is aggregated. It works fine as long as I do not park "Pick status" onto the worksheets. As you can see below, those that have 2 status code were marked as "Picking in Progress".

      Figure 1. Status working fine

      Capture.JPG

      Figure 2. Cannot put back pick status which I know why.

       

      My final objective is to obtain number of line under each status. Initially I tried to simply plot a pie chart however everything in status shows "Null" and I suspect it could be my calculated "Order status" is Aggregated.

      Hence instead, I try to count the numbers under each status. I am stuck in the calculation because I could not mix aggregated and non-aggregated in "IF". I could not force everything to be ATTR either due to COUNT cannot be further aggregated.

      Please help...

       

        • 1. Re: How to deal with Aggregate calculated dimention
          Zhouyi Zhang

          Hi, X H

           

          without seeing your workbook, it is not easy to give a accurate suggestion, but if you can try below code as your status code,

           

          { FIXED [Order Key],[Order Key & Line]:MIN(IF [Status (PICKDETAIL)] =

          { FIXED [Order Key],[Order Key & Line]:MAX([Status (PICKDETAIL)])} THEN [Status (PICKDETAIL)] END)}

           

          and then based on this calculation, create your second calculation as

           

          Countd(

          if [status code] = 'Allocated' then xxx end

          )

           

          Hope this helps

           

          ZZ

          • 2. Re: How to deal with Aggregate calculated dimention
            Xu Han

            Hi ZZ,

            Thank you so much for the help! I was inspired by your code and amended it a bit. It worked!

             

            IF { FIXED [Order Key],[Order Key & Line]:MIN([Status (PICKDETAIL)])} <>

            { FIXED [Order Key],[Order Key & Line]:MAX([Status (PICKDETAIL)])}

            THEN 'Pick in Progress'

            ELSEIF [Status (PICKDETAIL)]=0 THEN 'Allocated'

            ELSEIF [Status (PICKDETAIL)]=5 THEN 'Picked'

            END

             

            I could not attach the original workbook cos it was linked to database, nevertheless, I attached a sample workbook in case it might help the others.

            Btw, using min&max is great idea, however, I am just thinking, what if the status code is not number, but string (eg. A/B instead of 0/5), what could be the alternative?

             

            Many thanks

            • 3. Re: How to deal with Aggregate calculated dimention
              Zhouyi Zhang

              Glad it works for your case

               

              to your question, if the status comes in different format, like string, the workaround should be create a calculation to change the status into number, e.g.

               

              if [status code] = 'XYZ' then

                   0

              elseif [status code] = 'ABC' then

                   1

              .....

               

               

              end

               

              And then use this calculation in the above formula

               

              ZZ