3 Replies Latest reply on Apr 8, 2015 7:55 AM by pinky.thomas

    custom filter

    pinky.thomas

      Hi,

       

      I have a report which has a dimension called Report: Pending days. This captures reports that are late 1 day, 2 day, 3 day, upto 50 days.

      I want to create a custom filter for this dimension that enables me to pull all reports late than 1 day, late than 3 days, late than 5 days, late than 10 days, late than 20 days.

       

      I created a calculated field for the custom filter called Report Later than with the following formula IF float([Report: Days Pending after Meeting Date]) >= 1 THEN "1" ELSEIF float([Report: Days Pending after Meeting Date]) >= 3 THEN "3" ELSEIF float([Report: Days Pending after Meeting Date]) >= 5 THEN "5" ELSEIF float([Report: Days Pending after Meeting Date]) >= 10 THEN "10" ELSEIF float([Report: Days Pending after Meeting Date]) >= 20 THEN "20" ELSE "21+ Days" END

       

      But it is only showing reports that are more than 1 day late. Its not showing reports that are more than 3 days late, 5 days late etc., Attached is the attached twbx.

      I would like to see on the “Report late than” filter on the right hand side, the following options, 1, 3, 5, 10, 20.

       

      Do you know how to create such a custom filter?

       

      Regards

      P

        • 1. Re: custom filter
          Mark Fraser

          Hi Pinky

           

          Thinking out loud so bear with me...

          Example Report: Days Pending after Meeting Date = 25

           

          Because of the way the IF statement is written, your going to have a problem...

          is 25 >= 1, yes

          is 25 >= 3, yes

          is 25 >= 10, yes

          I won't go on, but you see the problem?

          At the moment it satisfies every requirement so it puts it in 1 as its first clause.

           

          Change the buckets so they are unique i.e. >=1 and <3, >=3 and <10 etc.

           

          ps.

          You can swap meeting date and today in the datediff calculation rather than *-1

          Good work by the way with the workbook.

           

          Cheers

          Mark

          • 2. Re: custom filter
            Felix Curran

            Hi Thomas,

             

            Mark Fraser is entirely right in why you are only getting back the greater than one result. And his solution is correct.

             

            Here is the calculation you need:

             

            IF float([Report: Days Pending after Meeting Date]) >= 1 AND float([Report: Days Pending after Meeting Date]) < 3

                THEN "1-2"

            ELSEIF float([Report: Days Pending after Meeting Date]) >= 3 AND float([Report: Days Pending after Meeting Date]) < 5

                THEN "3-4"

            ELSEIF float([Report: Days Pending after Meeting Date]) >= 5 AND float([Report: Days Pending after Meeting Date]) < 10

                THEN "5-9"

            ELSEIF float([Report: Days Pending after Meeting Date]) >= 10 AND float([Report: Days Pending after Meeting Date]) < 20

                THEN "10-19"

            ELSEIF float([Report: Days Pending after Meeting Date]) >= 20

                THEN "20+"

            ELSE "0"

            END

             

            Put that into your calculated field "Report Later Than" and it will give you the result you need.

             

            Regards,

            Felix

            • 3. Re: custom filter
              pinky.thomas

              Thanks Mark. I think I need to make clear one fact as well. There is double counting involved in this filter and hence it cannot be unique. As an example, if there are 25 meetings in total, out of which 5 are not late which means they are 0 late,  10 are more than 3 days late, 5 are more than 5 days late,  and 20 are more than 1 day late, we have to remember that the 20 that is more than one day late will also include the 10 that are 3 days late and the 5 which is 5 day late. So essentially, we are double counting here. If we make make the calculation unique, I am not sure if it will capture the right numbers.