9 Replies Latest reply on May 11, 2017 3:24 PM by Abhinav Kumar

    Aggregate Non-aggregate comparisons

    Abhinav Kumar

      Hi All,

       

      My apologies for the poor heading, cannot think of anything better!

      I am facing an issue where I am trying to consider 2 dimensions in a calculated field to calculate a score.

      The calculated field(Score) looks like this:

       

      IF [Is Sold]='Yes' AND [Status]='Return' THEN

      COUNT([State])*10

      ELSEIF [Is Sold]='No' AND [Status]='Return' THEN

      COUNT([State])*5

      END

       

      And the data set:

      IsSoldStatusState
      NoReturnCalifornia
      YesFulfilledCalifornia
      NoFulfilledNew York
      YesReturnNew York
      YesFulfilledCalifornia
      YesReturnCalifornia
      YesReturnNew York
      NoReturnNew York
      NoFulfilledTexas
      YesFulfilledTexas

       

      When I am trying to use the calculated field, it threw an error "cannot convert Aggregate and non-aggregate comparisons or results in 'IF' expressions"

      I have attached the workbook for reference.

       

      Any help is appreciated.

       

      Thanks,

      Abhi

        • 1. Re: Aggregate Non-aggregate comparisons
          Deepak Rai

          Hi Abhi,

          Your Error is gone but wont return anything as you are multiplying a String with Integar. Check your multiplication, then it will work.

          Thanks

          Deepak

           

           

           

           

          • 2. Re: Aggregate Non-aggregate comparisons
            Walt Reed

            Hi Abhi,

            You just need to put surround [Is Sold] and [Status] with MIN(). Effectively, you want to "aggregate" the two Dimensions so the aggregation (COUNT) on State can occur. Not sure if you want to do a COUNT or COUNTD, so I just followed your original formula.

             

             

            Walt

            1 of 1 people found this helpful
            • 3. Re: Aggregate Non-aggregate comparisons
              Abhinav Kumar

              Hi Walt,

               

              Thank you for the quick response. The way you showed will give me the correct response but not in a good visualization.

              I want to show only two columns i.e. State and Score.

              If I try to show AGG(Score) on Rows shelf it gives me incorrect calculation.

               

              Can you please help.

               

              Thanks,

              Abhi

              • 4. Re: Aggregate Non-aggregate comparisons
                Walt Reed

                Hi Abhi,

                My apologies, I didn't understand that was your final goal. I was able to figure out that the issue came from aggregating with the IF function, so I had success breaking the calculation into two calculated fields:

                1. Score1

                Assign a value of 1 if the Status = 'Return':

                2. Score2

                If/then statement to multiply Score1 by 10 or 5, depending on the value of [Is Sold]:

                3. Add the Measure to the viz and aggregate by SUM:

                Let me know if that works for you!

                 

                Walt

                1 of 1 people found this helpful
                • 5. Re: Aggregate Non-aggregate comparisons
                  Abhinav Kumar

                  Hi Walt,

                   

                  That was quick! Thank you for that!
                  This will indeed solve my problem. Just a QQ though, what if I have the following values:

                  In [Is Sold] column: <Yes, No, Maybe, I don't know, Something else>

                  In [Status] column: <Return, Not return, Fulfilled, Not fulfilled, Unsold, Sold>

                   

                  It will be helpful to know because my original data set might have the requirement like this in future!

                   

                  Thanks again!
                  Abhi

                  • 6. Re: Aggregate Non-aggregate comparisons
                    Walt Reed

                    Hi Abhi,

                    Glad to help. I'm not quite sure I understand your question. Are you asking if the calculations will be impacted if additional values are present in those two columns? If so, then no, the calculations won't be impacted.

                     

                    Did I interpret your question correctly?

                     

                    Walt

                    • 7. Re: Aggregate Non-aggregate comparisons
                      Abhinav Kumar

                      Hi Walt,

                       

                      My apologies for the confusion. Let me draw the table below to make you understand the data set:

                         

                      IsSoldStatusPriorityState
                      NoReturnP1California
                      YesFulfilledP2California
                      NoFulfilledP3New York
                      YesReturnP1New York
                      YesFulfilledP2California
                      YesReturnP3California
                      YesReturnP1New York
                      NoReturnP2New York
                      NoFulfilledP3Texas
                      YesFulfilledP1Texas
                      MaybeUnsoldP2Texas
                      I don't knowSoldP3Texas
                      Something elseIn progressP1Texas

                       

                      Currently, I am considering Yes/No from IsSold column and 'Return' from Status column. What if I have to consider all the values for my scoring and a new column called Priority is added, then how do I approach this.

                      One way I can think of is creating multiple calculated fields for each combination but that is not a feasible case!

                       

                      I hope I am able to articulate it better now!

                       

                      Thanks,

                      Abhi

                      • 8. Re: Aggregate Non-aggregate comparisons
                        Walt Reed

                        Hi Abhi,

                        I now see what you're saying. Regardless of the approach, there will obviously be some sort of manual input required. One approach you could take is to numbers to corresponding values in each field, then create one calculation that will multiply each of these together.

                        For example, assign values to Priority, Status, and Is Sold:

                        Then create a score that multiples the three:

                         

                        It's kind of hard to say on the front end and not knowing the data, but the basic concept applies.

                         

                        Walt

                        1 of 1 people found this helpful
                        • 9. Re: Aggregate Non-aggregate comparisons
                          Abhinav Kumar

                          Hi Walt,

                           

                          I agree! Thank you so much for your help!
                          I really appreciate it!

                           

                          Best Regards,

                          Abhi