6 Replies Latest reply on Dec 14, 2016 4:38 AM by Rennan Rocha

    Calculating sets

    Rennan Rocha

      In the Net Revenue's column there are 2 groups: Actual and Plan. What I did was create 2 sets, one of them called "Plan" and the other one "Actual".

      If I create a calculated field a error occour and I dont know how I can calculate the difference between actual vs plan.

      ERROR

      What I need to calculate (difference between "Actual and Plan"

       

      Tks

        • 1. Re: Calculating sets
          Andrew Watson

          Without knowing the error you're receiving (it would be helpful if you could also show that) you can try:

           

          SUM([Actual]) - SUM([Plan])

           

          I suspect that also may not work but without knowing the error it's difficult for me to give a more informed answer

          • 2. Re: Calculating sets
            Rennan Rocha

            Sorry Andrew.

            Here we go:

            "SUM is called (boolian); do you wanna call it (flutuant)?"

            I think that's occour because there's no numeric values in this field. They're categories. For exemple: I've Ebitda PLANO and REAL.

            • 3. Re: Calculating sets
              Andrew Watson

              maybe it should be SOMA and not SUM? But I expect that's not your problem...I've never tried to sum a set but I suspect you're trying to sum a boolean based on the message - i.e. True is in the set and False is out of the set.

               

              If your fields Actual and Plan are sets then you may have to revise how you have done things. Maybe changing to something like:

               

              SUM(IF [Actual] THEN [ActualFieldYouWantToSum] END) - SUM(IF [Plan] THEN [PlanFieldYouWantToSum] END)

               

              Alternatively you may want to rewrite your sets as calculated fields, then they're simple to use in other calculated fields.

               

              There are still too many unknowns in this equation for me to give a better answer.

              1 of 1 people found this helpful
              • 4. Re: Calculating sets
                Rennan Rocha

                Thanks, Andrew! I going to try explain this problem in a little bit more specific way..

                Talking about EBITDA, for example, I've two values. One value for "Plan" refering to the money that I expect to spend. And, the other value is "Actual", refering to the money the I really spent. Ok?

                In my database, the Fields "Plan" and "Actual" were inputed in the way bellow:

                In Tableau these values appears in this way:

                And I just can see "Actual" and "Plan" separately because I created 2 sets:

                Finally, what I wanna do is create a column with the difference between "Actual" and "Plan" not for only Ebitda.

                I tried to SUM(IF [Actual] THEN [ActualFieldYouWantToSum] END) - SUM(IF [Plan] THEN [PlanFieldYouWantToSum] END), but no values was returned.

                • 5. Re: Calculating sets
                  Andrew Watson

                  Try this: SUM(IF [Tipo] = 'REAL' THEN [EBITDA] END) - SUM(IF [Tipo] = 'PLAN' THEN [EBITDA] END)

                   

                  Not quite sure why you created sets for this but perhaps there's more to it than I can see. What this is doing is creating a separate formula for REAL and for PLAN.

                   

                  This is REAL: IF [Tipo] = 'REAL' THEN [EBITDA] END

                   

                  and this is PLAN: IF [Tipo] = 'PLAN' THEN [EBITDA] END

                  1 of 1 people found this helpful
                  • 6. Re: Calculating sets
                    Rennan Rocha

                    @Andrew Watson, thanks! Worked well

                    I just created a set because I can't see other way to do that.

                    I've value for PLAN and for ACTUAL to every single line of my income statement. Is it better create 2 differents databases?