7 Replies Latest reply on Aug 15, 2018 6:52 AM by Joe Wang

    countd() of mark causing unexpected problems

    Joe Wang

      Hi guys,

       

      I've setup a view as shown in below where I am showing the instances of the mark given what is in the Rows and Columns (Proportionality and Eng Partner). This is showing correctly.

       

      Capture.JPG

       

      I want to rather than showing all the names, just count the instances of the Eng names showing up - using the countd() function. However when I do this, I see some unexpected results:

       

      Capture2.JPG

       

      The count is correct in most rows, however for rows where there are names in more than 1 column, ALL of the count will fall into the lowest value Column.

      E.g. for Andrea Godfrey, rather than showing 1 for a 1-Score, and 1 for a 2-score, we see 2 for a 1-score.

       

      Can anyone provide some guidance as to what needs to be done to make the countd() show correctly? Normally believe the answer would be {fixed [Proportionality]: cntd(Eng Name/Code)}. However I can't use this as the Proportionality field is a rather complicated field that uses 2 data sources in the calculation, and is already aggregated as it benchmarks the Eng name score against score in a different data source.

       

      Thanks,

      Joe

        • 1. Re: countd() of mark causing unexpected problems
          David Maning

          hi,

           

          try use this:

          {fixed [score]: countd(smth)}

           

          trust this helps.

          D

          • 2. Re: countd() of mark causing unexpected problems
            Joe Wang

            Thanks. That is also what I originally wanted to do, but I get an error.

             

            Capture3.JPG

            "Level of Detail expressions cannot contain table calculations or the ATTR function"

             

            The field producing the error has the below formula:

            if

            min([LOD Engagement]) < (MIN([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)])+(1-min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)]))/5) and

            min([LOD Engagement]) > (min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)]) - min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)])/5)

            then '3'

            elseif min([LOD Engagement]) < (min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)])+(1-min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)]))/1.5) and

            min([LOD Engagement]) >(min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)]) - min([Sheet1 (HR_separatelist)].[ProportionalityGenderRatio (HR)])/1.5)

            then '2'

            else '1'

            end

             

            I cannot remove the min() because it return an error saying I need to either aggregate between different sources, or set to constant.

            • 3. Re: countd() of mark causing unexpected problems
              Jim Dehner

              Hi Joe -

               

              your data is coming from 2 sources - and we cant see in what you provided how you have connected the sources - please post your workbook

              additionally

              understand that LOD's are not aggregates and need to be further aggregated - so if you are using an LOD in one source and try to bring the result to a viz using another source it will be aggregated as you bring it in - the default is Sum() but can be changed to any of the aggregations - look at the formula that you use to bring it over and you can see the aggregation function

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: countd() of mark causing unexpected problems
                David Maning

                Can you please send a worksheet?

                • 5. Re: countd() of mark causing unexpected problems
                  Joe Wang

                  See attached. I am essentially trying to get 1 number per column, per row in the tab "Solution 1 Attempt".

                  • 6. Re: countd() of mark causing unexpected problems
                    Jim Dehner

                    hi Joe

                    this is what happens when the file is opened

                     

                     

                    you have 2 data sources that you are trying to blend but there are no relationship between the sources established in the book

                     

                     

                    don't know if that is what you meant to sent but without the linkage the 2 sources are independent - the values in one source have no meaning in the other source

                     

                    so take a step back - the 2 data sets are complex and based on what you are trying to do with data it is not clear how to tie them together ( i.e. is there a unique field that is common to each source that could be a join)- or do you really need to blend the data and create duplication in the data set?

                     

                    JIm

                    • 7. Re: countd() of mark causing unexpected problems
                      Joe Wang

                      Thanks Jim. I did not link the different data sources as I only need the HR Source for a constant value (a benchmark figure) which is calculated within the HR Source, and when filters are selected. This constant is then used in various calculations along with fields in the main data source.

                       

                      I've already been able to obtain the correct numbers in "solution 1". Just need to change the way it is outputted from "4 4 4 4", "2 2", "3 3 3" etc. to "4", "2", "3".

                       

                      Even narrowing the columns to show just the first instance of the number would work. Except there are some with value of 100 or more that show up as "##".

                      Capture4.JPG