8 Replies Latest reply on Jan 31, 2019 7:57 AM by Diogo Braga

    Exclude dates with bad data

    Diogo Braga

      The dataset has daily date stamps, but for the field 'recommits' there are certain days with bad data, hence the peaks and drops. What's the best way to clean that up on the report level?

       

      I need to exclude 556 records of bad data: 'academic year + date stamp'

       

      Here is what I tried:

       

      1. I created a parameter (Parameter 3] with data type string of all 556 records

      2019-01-29_1235.png

       

       

      2. Then a calculated field: if [Adacemic Year + Date]=[Parameter 3] then 'false' else 'true' END

       

      2019-01-29_1236.png

       

      3. Then applied filtered 'true' but only the first value got excluded, the one that shows as the current value in the parameter

       

      What do I have to do to exclude all the 556 records?

       

       

      2019-01-29_1223.png

        • 1. Re: Exclude dates with bad data
          Jim Dehner

          Good morning Diogo

          Parameters are single valued in application

          One way to do it is to join you list of dates to exclude with the existing and set the join clause to <>    (not equal)

          the join will look like a vlookup but exclude the records with matching dates

           

           

          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.

          • 2. Re: Exclude dates with bad data
            Sasha Hanna

            Hi Diogo,

            An easy way would be to create a visual group, select the points in the graph that are bad and filter them by clicking on exclude in the tooltip.

            Cheers,

            Sasha

            1 of 1 people found this helpful
            • 3. Re: Exclude dates with bad data
              Diogo Braga

              Thank you! The goal is to exclude the date for the recommit field only, not the date all together since other fields have good data that for the recommit is bad.

              In the example exclude the red values...

               

                  

              DateRecommitNew StudentsApplications
              2017-20182017-02-27020045
              2017-20182017-02-28132542
              2017-20182017-03-015025485
              2017-20182017-03-02428624
              2017-20182017-03-03375454
              • 4. Re: Exclude dates with bad data
                Diogo Braga

                Thank you ! I've tried that before and it works, but I am hoping to find something more systematic.

                • 5. Re: Exclude dates with bad data
                  Jim Dehner

                  OK that was a bad assumption on my part in that case you would create a file with the date and a simple indicator that they recommited

                   

                  and left join them on the date - you would then have an indicator tied to the records of the recommits - works like a lookup funcition in excel

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: Exclude dates with bad data
                    Diogo Braga

                    Thank you so much! Is that the only alternative? I would appreciate if you can explain visually...

                    • 7. Re: Exclude dates with bad data
                      Jim Dehner

                      It is the easiest because you have a long list of dates -

                      if it is just a couple of dates you could us an if statement that has a lot of clauses

                      like

                      Indicator ->

                      If [date] = first date

                      or [date] = second date

                       

                      or [date] = nth date]

                      then "Recomitted" end

                       

                      then if indicator = "Recomitted" then 0 end

                       

                       

                       

                      Jim

                      1 of 1 people found this helpful
                      • 8. Re: Exclude dates with bad data
                        Diogo Braga

                        This worked...thank you!

                         

                        if [Academic Year]='2017-2018'

                        and (([Date Stamp]>=#2/27/2017# and [Date Stamp]<=#3/7/2017#)

                        or ([Date Stamp]=#3/13/2017#)

                        or ([Date Stamp]=#7/25/2017#)

                        or ([Date Stamp]>=#7/29/2017# and [Date Stamp]<=#6/13/2018#)

                        or ([Date Stamp]>=#8/1/2018#))

                         

                        then INT('')

                         

                        elseif [Academic Year]='2018-2019'

                        and (([Date Stamp]>=#9/7/2018# and [Date Stamp]<=#9/10/2018#)

                        or ([Date Stamp]>=#10/16/2018# and [Date Stamp]<=#11/4/2018#))

                         

                        then INT('')

                         

                        elseif [Academic Year]='2019-2020'

                        and (([Date Stamp]>=#11/17/2018# and [Date Stamp]<=#12/19/2018#))

                         

                        then INT('')

                         

                        else [Recommit] END