4 Replies Latest reply on Mar 11, 2013 12:27 PM by Richard Grove

    Filter on calculated field: Count but not value?

    Richard Grove

      Goal: Show plan vs. actual revenue on a dual access chart.

       

      I thought this would be pretty easy. Table 1 is the GL Budget: It contains plan dollar amounts by year, month and account (equipment, food, service). That generates a nice stacked bar when plotted against the Year and Month. Table 2 is the Invoice Detail: It contains low level transaction data from which the revenue can be calculated. Taken separately the graph up very nicely.

       

      The problem come when I attempt to add the calculated field InvoiceYear (integer, year(InvoiceDate) to the filter card. I get a message that, instead of asking if I want to select a value, advises that I should use it to count. I'm stumped.

       

      Filter-InvoiceYear.jpg

        • 1. Re: Filter on calculated field: Count but not value?
          Dan Huff

          Richard--

           

          I assume that your Year is appearing in your Measures List. Tableau likely did this since upon connecting to your data it saw your year as a field which was nothing but numerical entries and therefore guessed it was a measure. I'd bet that if you were to drag and drop your InvoiceYear field from the Measures List to the Dimensions List and then were to use the resulting dimension on the filter shelf, you would see your problem fixed.

           

          Hope this helps,

           

          Dan

          • 2. Re: Filter on calculated field: Count but not value?
            Richard Grove

            Hi Dan. I thought about that and tried it both as a dimension (where I started) AND as a measure. Same result. I even checked the values thinking that perhaps the date was creating bad values but the domain seems correct.

             

            Filter-InvoiceYear_Value.jpg

             

            And strangely enough, when I use the Invoice Head FiscalYear field it gives me the same option. (The FiscalYear field you see in the Filter is the GL_Budget table's field.)

             

            Filter-FiscalYear_Value.jpg

            • 3. Re: Filter on calculated field: Count but not value?
              Dan Huff

              Since it looks like the field is coming from the secondary data source in this sheet, have you tried switching the relationships here so that the Combined Financials LIVE data connection is the primary? If you can use it as a filter as you desire with the data connection as the primary, it may just be a limitation of blending in as it stands currently in version 7.

               

              If this still doesn't fix it, try the following:

               

              1) Create a new calculated field using the same formula

              2) Immediately move the resulting field from the Measures to Dimensions list

              3) Then try and add this to the filter shelf

               

              Dan

              • 4. Re: Filter on calculated field: Count but not value?
                Richard Grove

                My guess is that I'm blending tables that doesn't specifically relate to each other: Revenue-Plan vs Revenue-Actual. I was trying to do a bar graph (Plan) with a line graph overlay (Actual). I've got to imagine that this is a pretty common practice, no?

                 

                Concept_BudgetVsActual.jpg