1 2 3 Previous Next 37 Replies Latest reply on Feb 27, 2013 8:51 AM by Sudam Shetty Go to original post
      • 15. Re: Graph issues
        Jackie Klein

        Thanks, I changed the Percentage of to None and get the same result as before.

        • 16. Re: Graph issues
          Sudam Shetty

          Try duplicate as crosstab . Select Show Row totals from the Analysis/Total menu. This will show you how it is calculating the %s.

          Then  remove the % values on your CNTD(CUSTID) field and see what the actual values are.

           

          If possible post screenshots of both wth and without %

          • 17. Re: Graph issues
            Jackie Klein

            Before % values removed:

             

            022613_5.png

            After % values removed - Grand Totals don't seem right.  For example, all 22 customers in Winston Salem had a value of  "3/4" value for AGG(MET_GOALS) for December (there weren't any customers that did not have a value of "3/4" for December.  Not sure why it shows 28 for Grand Total. 022613_6.png

            • 18. Re: Graph issues
              Sudam Shetty

              Couple of ques

              1. Is total distinct CUST_IDs  in Winston Salem -  22 ?

              2. does FUNCTION_TEXT filter you view for values MET_GOAL 3 and 4?

               

               

              If both are true, try creating this view from scratch. That should clear out the unwanted background work that tableau is doing..

              • 19. Re: Graph issues
                Jackie Klein

                1.  22 is the distinct count of CUST_IDs in Winston Salem that have a MET_GOAL value for December (see chart below).

                 

                022613_7.png

                 

                28 is the distinct count when I remove MY(PERIOD_REPORT) from the Columns shelf and move it to the filter and select all months in the filter.

                 

                022613_8.png

                2. FUNCTION_TEXT filters for a specific function (i.e. "Telephone") - not for MET_GOAL value.  Using this filter shows all CUST_IDs with the FUNCTION_TEXT = "Telephone". 

                • 20. Re: Graph issues
                  Sudam Shetty

                  In that case you need to have MET_GOAL in your filter and select just 3/4.

                  So the way it is calculating is 

                  (Distinct CUST ID in Dec) / (Distinct CUST ID for all period).

                  Is this not how you want it?

                  • 21. Re: Graph issues
                    Jackie Klein

                    No, I want it to show by month, how many distinct CUST_IDs are 3/4 of the total CUST_IDs for that month.  So 22 out of 22 total for December should show 100%.

                    • 22. Re: Graph issues
                      Sudam Shetty

                      Heres another approach:

                       

                      1.

                      Create another Calculated field CUST_PERSON_ID(3/4) with the formula

                      IF MET_GOAL = "3/4" THEN [CUST_PERSON_ID] ELSE NULL END

                       

                      2. Create another field for your percentage calculation:

                      COUNTD(CUST_PERSON_ID(3/4)) / COUNTD(CUST_PERSON_ID)

                       

                      Use the field created in step 2 in your rows shelf.

                      This should get you where you need to be

                      • 23. Re: Graph issues
                        Jackie Klein

                        I tried to create a calculated field as follows, but I get an error "cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions". 

                         

                        IF MET_GOAL = "3/4" THEN [CUST_PERSON_ID] ELSE NULL END

                         

                        So I did this instead, however this gives me the total count of all customers intead of just the count of customers that have "3/4".

                         

                        IF [MET_GOAL] = '3/4' THEN COUNTD([CUST_PERSON_ID])

                        ELSE Null

                        END

                        • 24. Re: Graph issues
                          Sudam Shetty

                          whats the calculation behind MET_GOAL?

                          • 25. Re: Graph issues
                            Jackie Klein

                            MET_GOAL:

                            CASE [GOAL]

                            WHEN 1 THEN '1/2'

                            WHEN 2 THEN '1/2'

                            WHEN 3 THEN '3/4'

                            WHEN 4 THEN '3/4'

                            ELSE Null

                            END

                            • 26. Re: Graph issues
                              Sudam Shetty

                              Use GOAL in your IF function for <CUST_PERSON_ID(3/4)>


                              IF GOAL = "3" THEN [CUST_PERSON_ID]

                              ELSEIF GOAL = "4" THEN [CUST_PERSON_ID]

                              ELSE NULL END

                              This should take care of the Mixing Agg wth Non-Agg error.

                               

                              then for your %

                              COUNTD(CUST_PERSON_ID(3/4)) / COUNTD(CUST_PERSON_ID)

                              • 27. Re: Graph issues
                                Jackie Klein

                                I still get the agg with non-agg error using the revision you suggested.  Not sure if it's because GOAL is a calculated field?

                                 

                                GOAL:

                                IF isnull([T/F_metric1]) THEN 0 ELSE IIF([T/F_metric1]=TRUE,1,0)

                                END

                                +IF isnull([T/F_metric2]) THEN 0 ELSE IIF([T/F_metric2]=TRUE,1,0)

                                END

                                +IF isnull([Table2].[T/F_metric3]) THEN 0 ELSE IIF([Table2].[T/F_metric3]=TRUE,1,0)

                                END

                                +IF isnull([Table2].[T/F_metric4]) THEN 0 ELSE IIF([Table2].[T/F_metric4]=TRUE,1,0)

                                END

                                • 28. Re: Graph issues
                                  Sudam Shetty

                                  Yup. its because GOAL is calculated

                                   

                                  Can you try this for 

                                  <CUST_PERSON_ID(3/4)>

                                   

                                  IF

                                  IIF([T/F_metric1]=TRUE,1,0) +

                                  IIF([T/F_metric2]=TRUE,1,0) +

                                  IIF([Table2].[T/F_metric3]=TRUE,1,0) +

                                  IIF([Table2].[T/F_metric4]=TRUE,1,0)      >=3

                                  THEN [CUST_PERSON_ID]

                                  else NULL END


                                  • 29. Re: Graph issues
                                    Jackie Klein

                                    I tried that, and same error - Agg with non-agg.

                                     

                                    The T/F fields are calculations as well:

                                     

                                    T/F_metric1:

                                    MIN([GOAL_metric1])<=[metric1]

                                     

                                    The GOAL_metric1 field is also a calculated field.