7 Replies Latest reply on Sep 9, 2016 6:20 AM by Sandeep Kotha

    Calculation field Problem??

    Sandeep Kotha

      Hi All,

       

      Could someone reply for the below question? I have the below fields.

       

      1.New:  Before,After,No

      2.ID   : All ID's .This can have repetition.

      3.Rating: 0 to 10:

      4.Domain: Yes/No

       

      I have the data in such away that : When Domain=No then will be 'No' in all the fields otherwise same field data it has.Below is sample data:

        

      DomainIDNEWRating
      Yes25997Before1
      NoNoNoNo
      NoNoNoNo
      NoNoNoNo
      Yes34951After9
      NoNoNoNo
      NoNoNoNo
      NoNoNoNo
      NoNoNoNo
      Yes17827After7
      NoNoNoNo
      Yes39190Before10
      Yes75330Before3
      Yes70002Before6
      NoNoNoNo
      NoNoNoNo
      Yes56249After6
      NoNoNoNo
      Yes45386Before4
      Yes48165Before8
      NoNoNo9
      NoNoNoNo
      Yes5865After5

      and so on.

       

      What I want:

      First I have to select similar ID's who are in both "Before" and "After" levels and then find the below points.

       

      IF

      Rating(After) > Rating(before) then 'Positive'

      Rating(After) < Rating(before) then 'Negative'

      Rating(After) = Rating(before) then 'NoChange'

       

      and Then I have to find the counts and %'s of each status as below:

       

        This is the final output that I want:

      Count%
      Positive550%
      NoChange220%
      Negative330%
      TOTAL10100%

       

       

       

      What I have done: I created the three fields as shown below

       

      AfterScore:    If NEW='After' then [Rating] END

      BeforeScore: If NEW='Before' then [Rating] END

       

      Status:

      if MAX([AfterScore])>max([BeforeScore]) then 'Positive'

      ELSEIF MAX([AfterScore])<MAX([BeforeScore]) then 'Negative'

      ELSEIF MAX([AfterScore])=MAX([BeforeScore]) then 'No Change' else 'NULL' END

       

      Now, When I drag the field called "Status" on the rows shelf, I got the below output.

      It has only one level "No Change". Also, I am unable to find the count(AGG(Status)).

       

      I know I have done some mistake or might be that my approach is wrong. But i am unable to identify my mistake what I have done.

       

      Could someone help me on this issue with the correct code for my output.

       

        This is the final output that I want:

       

      Count%
      Positive550%
      NoChange220%
      Negative330%
      TOTAL10100%

       

      Thank you.

       

      Best Wishes,

      Sandeep

        • 1. Re: Calculation field Problem??
          Luciano Vasconcelos

          You need to aggregate rating to do it.

          You can't have no as rating value. It should be numeric.

          I improved your sample of data.

           

            

          DomainIDNEWRating
          Yes5865After5
          Yes17827After7
          Yes25997Before8
          Yes34951After6
          Yes39190Before6
          Yes45386Before3
          Yes48165Before8
          Yes56249After6
          Yes70002Before5
          Yes75330Before7
          Yes5865Before1
          Yes17827Before7
          Yes25997After1
          Yes34951Before9
          Yes39190After10
          Yes45386After4
          Yes48165After8
          Yes56249Before6
          Yes70002After6
          Yes75330After3
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNoNo
          NoNoNo9
          NoNoNoNo
          • 2. Re: Calculation field Problem??
            chris.moore.11

            Hi, you have a good line of thought going. One issue is your "max" function is being applied across everything the way you have it now. All your current result is saying is the highest overall "Before" is the same as the highest overall "after" in the set. What happens if you drag ID into the row column before that aggregation?

             

            One way to deal with it is to use an lod to apply it to [ID]. Like...

             

            if  { fixed [ID] : MAX([AfterScore]) } > { fixed [ID] : max([BeforeScore]) } then 'Positive'

            ELSEIF { fixed [ID] : MAX([AfterScore]) } < { fixed [ID] : MAX([BeforeScore]) } then 'Negative'

            ELSEIF { fixed [ID] : MAX([AfterScore]) }= { fixed [ID] : MAX([BeforeScore]) } then 'No Change' else 'NULL' END

             

            I'll try to post a workbook that could help.

            -Chris

             

            Edit: Attached a workbook, although not sure if it will help. "After Score" and "Before Score" are the fields you created, "Status" is the new calculated field.

            1 of 1 people found this helpful
            • 3. Re: Calculation field Problem??
              Mahfooj Khan

              As per my understanding your count should be like this. Others coming from where you've ID='No' and as per your logic you're only considering ID with After or Before. But I've noticed you've count the IDs with 'No' also. So, I've put it in others. Hope you understand.

              and % of Total Should be

              Go through the workbook (version 9.3) attached for your reference. Feel free  to ask If you've any query.

               

              Mahfooj

              1 of 1 people found this helpful
              • 4. Re: Calculation field Problem??
                Sandeep Kotha

                Hi Chris,

                 

                Thank you very much for your response.It worked for me perfectly.I am happy.

                But i am getting Null as well as shown below. How to remove this NULL.

                 

                 

                 

                Can you please help me with this?

                 

                Best Wishes,

                Sandeep

                • 5. Re: Calculation field Problem??
                  Sandeep Kotha

                  Hi Mahfooj,

                   

                  Thanks for the reply. I just replied to Chris. Could you please answer my question if you know?

                   

                  Best Wishes,

                  Sandeep

                  • 6. Re: Calculation field Problem??
                    Mahfooj Khan

                    I guess its coming for those IDs which are 'No'

                    1 of 1 people found this helpful
                    • 7. Re: Calculation field Problem??
                      Sandeep Kotha

                      Got it. I just used Status in the filter and removed NULL. Very helpful answers.

                       

                      Thanks once again.

                       

                      Best Wishes,

                      Sandeep