6 Replies Latest reply on Nov 14, 2018 4:27 AM by seif saleh

    Grouping multiple dimension values

    seif saleh

      Hi,

      I struggling to find a solution for the below, would really appreciate some help on this

       

      I have the following table:

       

      IDSub Funnel GroupSub funnel start dateSub funnel End Date
      Funnel Group
      1Initial Screening 1/1/20171/2/2017Applied
      1Interview1/2/20171/3/2017Screened
      1Approved1/3/20171/4/2017Screened
      2Initial Screening 1/1/20171/2/2017Applied
      2Rejected
      1/2/2017
      1/4/2017Screened
      3Initial Screening
      1/1/2017
      Applied

       

      I am trying to create new dimension values based on the following:

       

      if Sub Funnel Group = "Initial Screening"  and Sub Funnel Group = " Approved"  then new dimension = "Approved"  ( ID = 1)

      IF Sub Funnel Group = "Initial Screening"  and Sub Funnel Group = " Rejected" then new dimension = "Rejected" (ID = 2)

      if sub funnel end date is null then new dimension = Funnel Group Value (ID = 3)

       

      I am also trying to map the results to the funnel group, but the issue is that I will have duplicate values of the new calculated dimension for the ID

       

       

      I am trying to have an end result which looks like this:

       

       

       

        • 1. Re: Grouping multiple dimension values
          Zhouyi Zhang

          Hi, Seif

           

          Without seeing a sample workbook, I just can make up the calculation based on your description, but can't test it

          Below is the calculation you may have a try

           

          if isnull({Fixed [id]:max([Sub funnel End Date])}) Then

          [Funnel Group]

          elseif (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Initial Screening' then [Sub funnel Group] end)}))

          and

          (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Approved' then [Sub funnel Group] end)})) then

          'Approved'

          elseif (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Initial Screening' then [Sub funnel Group] end)}))

          and

          (Not isnull({Fixed [id]:max(if [Sub funnel Group] = 'Rejected' then [Sub funnel Group] end)})) then

          'Rejected'

          end

           

          Hope this helps

           

          ZZ

          • 2. Re: Grouping multiple dimension values
            seif saleh

            Hi Zhouyi,

             

            Thanks so much for your reply ! This is so helpful.

             

            I am also trying to mark the latest Sub funnel Group for each ID and Funnel Group

             

            For example:

             

            ID 1: 

            Initial Screening is the final step in Applied Funnel Step

            Approved is the final step in Screened Funnel Step

             

             

            Thank you so much for your help

            • 3. Re: Grouping multiple dimension values
              Zhouyi Zhang

              Hi, Seif

               

              I am glad to help. if you could make up some dummy data in a workbook, and explain the expected result, this will be easier for me to provide help

               

              ZZ

              • 4. Re: Grouping multiple dimension values
                seif saleh

                Hi Zhouyi,

                 

                Thanks some much for your help

                 

                Please find attached.

                 

                The expected outcome is the following:

                1. The user selects a time frame.
                2. If the sub funnel start data is in this frame and the subfunnel name is "1 - Applied" and if there are no other subfunnel steps (within the funnel group) for each user; then this will be called "live".
                3. If the sub funnel start data is in this frame and the subfunnel name is "1 - Applied" and if there are other subfunnel steps (within the funnel group) for each user; then this row should be filtered out.

                 

                Thank you !!

                • 5. Re: Grouping multiple dimension values
                  Zhouyi Zhang

                  Hi, Seif

                   

                  thanks for your workbook. I am bit of confused that in your sample data, I didn't find any cases that have a sub funnel name with '1-Applied' only as highlighted below.

                   

                  Is something I mis-understand?

                   

                   

                  ZZ

                  • 6. Re: Grouping multiple dimension values
                    seif saleh

                    Hi Zhang,

                     

                    Thanks for your reply.

                     

                    For example candidate 15 has only 1-Applied' in the subfunnel linked to Applied funnel group and no other sub funnel steps linked to applied

                     

                    for candidate 5

                     

                    We have 1-Applied' in the subfunnel linked to Applied funnel group and rejected app linked to applied, so: the row with 1-Applied will be filtered out and only the row with rejected app will remain.

                     

                    Hope this is clear.

                     

                    Please let me know if you have any questions. I really appreciate your help

                     

                    Seif