7 Replies Latest reply on Aug 15, 2016 6:13 AM by Rob Rimmer

    SUM for only positive values?

    Rob Rimmer



      I am creating a dashboard to show the amount of sales we need to make in order to meet targets for the business. I have a calculation to find the number of sales we want to reach (last year's sales * 1.1) and know the current sales numbers for this month. I have a calculated field that subtracts the current sales from my target to show the amount remaining. The calculation returns 0 if the value of the expression is less than 0.


      The data has rows that can display either product type, region or customer. I know that the total remaining to sell should differ depending on which dimension I'm looking at but Tableau returns the same total to sell regardless.


      Is there a calculation I can use to get a true number needed depending on the view I use?


      Apologies for the lack of example dashboards, but I'm conscious that the data is fairly sensitive.


      Thanks in advance

        • 1. Re: SUM for only positive values?
          Tom W

          Hi Mr Rimmer (there's a bug on the forums right now which is only showing last names, I don't normally choose to be this formal )


          It's possible to sum only positive values by creating a calculated field like; SUM(IF [Sales] >0 then [Sales] end)



          I'm not sure if this is going to actually solve your problem though as it's really tough to follow what you're speaking about without sample data. If it's not quite getting you there, please prepare a trimmed down version of your data. It doesn't need to be real, but keep the columns the same etc, connect that to Tableau and upload a Tableau Packaged Workbook.

          • 2. Re: SUM for only positive values?
            Derrick Austin

            Hey Rimmer,


            Would it be possible to share the calculations you are using and a bit more about the results you are seeing/the results you'd like to see?


            I'm not sure I follow exactly what you are needing.



            - Derrick

            • 3. Re: SUM for only positive values?
              Rob Rimmer

              Hi Tom,


              I didn't realise first names were being blocked. My name is Rob. I've not had laptop access over the weekend so couldn't reply to your message. I've tried the calculated field you suggested but it didn't work. I'll endeavour to create a trimmed down version of the data to try to help, but I'm not sure how quickly I'll be able to do this.



              • 4. Re: SUM for only positive values?
                Rob Rimmer

                HI Derrick,


                I have:


                Product type:                         Product being sold

                Region:                                  Sales region

                Customer:                              Customer name

                Month:                                   Month of sale

                Sales:                                    Number of sales so far in the month

                Sales Prv Yr:                         Sales for month in previous year

                Sales target:                          Sales Prv Yr * 1.1

                Remaining to Sell:                 Sales target - Sales

                Remaining to Sell (copy):      IF Sales target - Sales < 0 THEN 0 ELSE Sales target - Sales END


                My issue is that the Grand Total on the crosstab doesn't seem to take the dimension (region, customer or product type) into consideration. i.e. customers that we have oversold to seem,to mask sales that aren't going to meet target elsewhere.


                Sorry for the lack of clarity.



                • 5. Re: SUM for only positive values?
                  Tom W

                  Hi Rob,

                  Our usernames are functioning again


                  Please upload the same, I'll need to see that to understand why it's 'not working' or I'll at least need more details. A Tableau Packaged Workbook is honestly the easiest place to start.

                  • 6. Re: SUM for only positive values?

                    Love how Derrick shows as Derrick Austin haha!

                    • 7. Re: SUM for only positive values?
                      Rob Rimmer

                      Thanks Tom,


                      I'm going to try to create a simplified version of the table(s) and put them into a Tableau workbook but it won't be until tomorrow as I have some other deadlines to meet. As soon as I have it complete, I'll upload it.