7 Replies Latest reply on Apr 23, 2013 7:32 AM by dhveryoldaccount

    calculated field with count

    Jason Back

      Hello,

       

      This may be a very simple questions but I'd like to know how to a create calculated field measure that counts how may stores are sales over 10 in each division.

       

      Simple example is attached.

       

      Thank you,

      Jason

        • 1. Re: calculated field with count

          Hey Jason I've attached what I think you might want. I included an parameter to set the "sales over 10" to be "sales over parameter". Thought that'd be a little more flexible for any future projects. The second tab does the same but included a total store count in the grand total. Not sure if that's what you were looking for but hopefully this helps a little. Let me know if that will work or if you have questions.

          • 2. Re: calculated field with count
            Jason Back

            Hi Derek,

             

            Thank you so much for the anwser. It it certainly good to know tha way. Couple questions:

             

            In the file I am working, Sales is actually a calcualted field and when I use the same calucaltion it says that "Cannot mix aggregate and non aggregate comparasions or results in 'if' expressions" so I tried to add ATTR[Store] and the calculation was ok but cannot drag to filter box..

             

            In the second tab you created, what I need is a measure of calculated field that directly returns a value for the number of east division stores that had sales over 10 in 2013. So example in the tab would be "3".  Would there be a calculations to make this?  Thank  you so much.

             

            Jason

            • 3. Re: calculated field with count

              What does the calculated field for sales look like? Can you copy/paste it so I can see what you are doing? My first thoughts are to disaggregate the sales measure but I'll need to see how that could be done first. I've attached an update to the workbook that replaces the store filter from earlier and just returns a value for each division.

              • 4. Re: calculated field with count
                Jason Back

                Hi Derek,

                 

                There are certain terms that my industry uses so I will just use alpahbet letters to illustrate.

                 

                So the calculated field for Sales is: ([A]/[B])*100

                where A is: sum([C])/sum([D])

                where B is: sum([E)/sum([F])

                C, D, E, F are all continues measures, database column type and sum default aggregation.

                 

                So for the filter, when I write: if [Sales] >=10 then [Store] end, it gives me an error saying "Cannot mix aggregate and non aggregate comparasions or results in 'if' expressions".

                 

                Thank you!

                Jason

                • 5. Re: calculated field with count

                  Will you get the same sales results if you remove the sum function from C, D, E, and F? Such that...

                   

                  A = [C]/[D]

                  and

                  B = [E]/[F]

                   

                  Try creating a duplicate of A and B. Then in the copy remove the sum functions. That way you're not messing with something that already works.

                   

                  If the copies don't mess up the sales calculation, try substituting in that version of the sales calculation with either the first or second version of the attached workbooks.

                  • 6. Re: calculated field with count
                    Jason Back

                    Unfortunately, getting rid of sum fuctions does not represent correct figure for sales... I think the reason why is because data has to be presentated at an aggreate level. For example, in order to calcuate and display something like profit margin at year/quarter/month levels, total sales and cost need to be calcualted at at aggreagate level for respective time period..

                    • 7. Re: calculated field with count

                      Well I'm stumped. It's the aggregation that is throwing me off. I thought there was a workaround that would get exactly what you wanted but I'm at a loss. However, you could do something like create a crosstab and export/copy that to an Excel spreadsheet and then connect Tableau to that sheet. Then you'd have the aggregates that you need but once you connect to the new spreadsheet they'd be disaggregated and the solution from earlier would work. Just a thought.