7 Replies Latest reply on Feb 23, 2016 10:05 AM by Shinichiro Murakami

    Need help in displaying custom filter

    vinutha vasanthu

      I have a data in the following format.

       

            

      Postal CodeStateTimeDayRoad TypeNumber of Accidents
      10003NYDSundayUrban20
      10021NYBMondaySemi urban34
      10041NYDTuesdayRural15
      10113NYCWednesdayUrban48
      10150NYDThursdaySemi urban99
      10160NYEFridayRural146
      10152NYDSaturdayUrban201
      10162NYASundaySemi urban4
      10154NYAMondayRural6
      10164NYATuesdayUrban7

       

      My Question is how to display the time in the following format.

       

      When "A" then [6AM-9:59AM]

      When "B" then [10AM-3:39PM]

      when "C" then [4PM-7:59PM]

      when "D" then [8PM-11:59PM]

      When "E" then [12AM-5:59AM]

       

      In the sense the filter drop down should have

      [6AM-9:59AM]

      [10AM-3:39PM]

      [4PM-7:59PM]

      [8PM-11:59PM]

      [12AM-5:59AM]

       

      Solutions please....

        • 1. Re: Need help in displaying custom filter
          pooja.gandhi

          You could do a CASE Statement:

           

          CASE [Time]

          When "A" then "[6AM-9:59AM]"

          When "B" then "[10AM-3:39PM]"

          when "C" then "[4PM-7:59PM]"

          when "D" then "[8PM-11:59PM]"

          When "E" then "[12AM-5:59AM]"

          END

           

          Drag it to filter and select show filter.

          • 2. Re: Need help in displaying custom filter
            Shinichiro Murakami

            Maybe I'm wrong, but are you asking to show simply below?

             

            [Time Dsiplay]

            case [Time]

            When "A" then "[6AM-9:59AM]"

            When "B" then "[10AM-3:39PM]"

            when "C" then "[4PM-7:59PM]"

            when "D" then "[8PM-11:59PM]"

            When "E" then "[12AM-5:59AM]"

            END

            • 3. Re: Need help in displaying custom filter
              vinutha vasanthu

              Hey Shin,

               

              Yea instead of A,B,C,D  want the time be in displayed in the range as specified above.

              I did it using vlookup in excel.I am trying to see if there is a way display in the filter as 6AM to 9:59AM..etc

               

              Thanks

              Vinutha

              • 4. Re: Need help in displaying custom filter
                Alec Barrett

                If you drag the [Time Display] calculated field to the filter shelf, and show the Quick Filter, that should display the values "6AM-9:59AM", "10AM-3:39PM", etc.

                • 5. Re: Need help in displaying custom filter
                  Shinichiro Murakami

                  Vinutha,

                  Thank you for the reply.

                  It's quite difficult understand underlying your needs.

                  Why you need to convert ABCD to time range?

                  What is your final goal in this case?

                   

                  Could you share the info with some screenshot or excel or something?

                   

                  Shin

                  • 6. Re: Need help in displaying custom filter
                    vinutha vasanthu

                    Hi Shin,

                     

                    This is the data i was provided with

                         

                    Postal CodeStateTimeDayRoad TypeNumber of Accidents
                    10003NYDSundayUrban20
                    10021NYBMondaySemi urban34
                    10041NYDTuesdayRural15
                    10113NYCWednesdayUrban48
                    10150NYDThursdaySemi urban99
                    10160NYEFridayRural146
                    10152NYDSaturdayUrban201
                    10162NYASundaySemi urban4
                    10154NYAMondayRural6
                    10164NYATuesdayUrban7

                     

                    I know the data is very confusing and the data i was provided is manually entered data.I am not sure why they are using time as alphabets.For them entering the alphabet was much easier than entering the time range for each individual row.

                    Now they want the time to displayed in this range

                    "A" then "[6AM-9:59AM]"

                    "B" then "[10AM-3:39PM]"

                    "C" then "[4PM-7:59PM]"

                    "D" then "[8PM-11:59PM]"

                    "E" then "[12AM-5:59AM]"

                    So your filter should have [6AM-9:59AM],[10AM-3:39PM],[4PM-7:59PM],[8PM-11:59PM],[12AM-5:59AM] instead of A,B,C,D,E.

                     

                    So now what i want is create a custom filter with these range and link the ABCDE to these range.

                    My dashboard filter should display [6AM-9:59AM],[10AM-3:39PM],[4PM-7:59PM],[8PM-11:59PM],[12AM-5:59AM]

                     

                    Thanks

                    Vinutha

                    • 7. Re: Need help in displaying custom filter
                      Shinichiro Murakami

                      Vinutha,

                       

                      Sorry for you to get complicated request.

                       

                      Exact that part is making me confused.

                      I mean Range cannot be single line. 

                      In other words,  the line item is the event that happened at specific "time" , then the filter should filter line items by "Time" itself.

                       

                      Now A,B,C,D is not time associated, already "Range" associated, which means there is not reason yo uneed to convert these back tom "time".

                      Only chaging the description seems enough for me, or I might still miss some important part.

                       

                      Thanks,

                      Shin