6 Replies Latest reply on Apr 1, 2016 7:57 AM by harris.tsr

    Display the available numbers for one out of many attributes of one filter

    harris.tsr

      Hi Community,

       

      I'm trying to create a new dashboard, relatively simple, but I'm stach in a really simple thing. I want to display the number ONLY OF THE NULL Master_Reject_Reason only for the avalable tactics. But I cannot isolate the count of the nulls, because even when I create calculated field, it takes all the numbers and reject reasons. This is critical, because I will need to add columns that we'll be based on the number of the "null" reject reasons and has a knock effect on the results.

       

       

      I'd much appreciate your help

       

      Regards,

      Harris

        • 1. Re: Display the available numbers for one out of many attributes of one filter
          Simon Runc

          hi Harris,

           

          I think I understand what you are after...let me know if I've misunderstood (certainly won't be the last time).

           

          One way to go about this is to create a row level IF statement and then aggregate the result of that

           

          So in your example it would be

          [Master_Reject_Reason - NULL Count]

          SUM(IIF(ISNULL([Master_Reject_Reason]),1,0)

           

          Hope that does the trick.

          • 2. Re: Display the available numbers for one out of many attributes of one filter
            harris.tsr

            Hi Simon,

             

            Thank you so much for your reply. I think we are very close to get to the bottom of that. I reckon I've done it correctly, because I get the same numbers as in my initial screenshot. Next I want something similar but requires to include both the nulls and the 0PEN GRS as follows:

             

             

            I have removed the AGG you suggested because when I add the CALL_DISPOSITION_CODE_CALCULATED on the columns it is really messed up, but if I try to create a similar calculation I will be able to have it in the "Measure names" shelf. Would you be so kind to advice on how I can create a calc that would only keep the Nulls and the 0PEN GRS?

             

            Tons of thanks

            • 3. Re: Display the available numbers for one out of many attributes of one filter
              Simon Runc

              Cool.

               

              So yes you can just create the calculated field as

              [Master_Reject_Reason - NULL Count]

              IIF(ISNULL([Master_Reject_Reason]),1,0)

               

              and then when you use it in the Viz you can then aggregate (and choose the aggregation, if needed)

               

              So we can just expand the same logic to also include the OPEN GRS

              [Master_Reject_Reason - NULL/OPEN_GRS Count]

              IIF(ISNULL([Master_Reject_Reason]) OR [Master_Reject_Reason]='OPEN GRS',1,0)

              • 4. Re: Display the available numbers for one out of many attributes of one filter
                harris.tsr

                Hi Simon,

                 

                Thanks for this. Maybe I wasn't clear enough, let me give you an overview og the whole idea so as to hopefully give you a full understanig of it.

                 

                In the first column, correct, we want to viz only the reject reasons that fall into the null bucket. The second column we need to display from the field named "CALL_DISPOSITION_CODE_CALCULATED" only these that fall into OpenGRS and NULL as well.

                I will need a third column which will display the positive dispositions, and in doing so, again will need to have "isolated" values from the same CALL_DISPOSITION_CODE_CALCULATED. These values will be: call in progess/call back/contacted.

                 

                Please see the mock up in a spreadhseet which I hope will help you understand what I want to vizualise.

                 

                 

                What I don't quite get from your second reply is if this would be possible to be created on one single calculation or I should need more that one in order to achieve this view.

                 

                Thank you very much for your time

                • 5. Re: Display the available numbers for one out of many attributes of one filter
                  Simon Runc

                  So yes, the best way would be to create 3 calculated fields. As you want one Viz, but the 3 measures are (in essence) filtered in 3 different ways (you want the 1st measure filtered on NULLs, the second on NULL or OPEN GRS...etc.) you can't use 'actual' filters. Embedding the filtering in the IF statement, as we have done here, lets you have different (pseudo) filtering for different measures.

                   

                  Hopefully the attached will help show you what I mean...in this I've create the COUNT of records for each of the 3 categories in Superstore. In this situation I would usually, of course, just use 'Number of Records' and then use the Category dimension on the columns, but for the puposes replicating your situation this should show you the theory.

                  • 6. Re: Display the available numbers for one out of many attributes of one filter
                    harris.tsr

                    You have probably saved my life. I need to confirm with the stakeholders and check the validity of the data but it definitely looks better and I've understood the logic of nesting IF statements.

                     

                    Thank you very much