13 Replies Latest reply on Apr 29, 2018 8:10 AM by Don Wise

    If function, Aggregation, Filter, Fixed value

    Hessam Harandi

      Hello everyone,

       

      I want to find the houses that received citation(s) without prior warning. and put a filter of time for the tickets issued, but it shouldn't limit the time of receiving warning.

      the logic is as following:

       

      if  ticket type="Citation"  and  Ticket Type<>"Warning"  then show address. Note that I need to have a date filter for my worksheet but it shouldn't affect the receipt of warning. In an other word, see the home addresses received citation in e.g. last week but they have not received warning at all time.

       

      I have attached part of the data.

      Ticket types are abbreviated as K for citation and W for warning.

       

      Thanks for your help in advance!

        • 1. Re: If function, Aggregation, Filter, Fixed value
          Don Wise

          Hi Hessam,

          See if the below screenshot and attached workbook will meet your requirement? If so, please mark this thread as correct (the post, not the email) so that others can find and use the solution.  Thx, Don

          Screen Shot 2018-04-25 at 10.24.15 AM.png

          1 of 1 people found this helpful
          • 2. Re: If function, Aggregation, Filter, Fixed value
            Hessam Harandi

            Hi Don,

             

            I have got one question. As I said, if I filter the date for last month for example, it will show citations issued without prior warning without considering if there is any warning in a time before last month.

            Do you have any recommendation for this issue?

             

            example:

             

            address: 1400 new hampshire ave      

            Citation: 1     date: 4/12/2018

            Warning: 1    date: 1/2/2015

            if I filter the date by current month it with shows me this address as an instance of citation without prior warning.

             

             

            Thanks,

            • 3. Re: If function, Aggregation, Filter, Fixed value
              Don Wise

              Hi Hessam,

               

              Apologies, as i didn't realize you'd be going backwards that far...I'll see what I can do tomorrow or next day...unless someone else jumps in on this with ideas first.  You're correct that in the workbook I supplied, you're only seeing Citations for the period within the view.

               

              This will probably need an LOD to reach back to the minimum date and maximum date for each Address.  It's difficult to do LOD's on string values and I think this will require using the particular Address field to be excluded from the view if the Address has both a Citation and a Warning at any time, if I understand correctly? 

               

              You want to be able to see just those Addresses that have Citations only regardless of the time of view.

               

              Thanks, Don

              • 4. Re: If function, Aggregation, Filter, Fixed value
                Hessam Harandi

                Hi Don,

                 

                Yes what you explained is exactly what I meant. I really appreciate it if you can find a solution for this.

                 

                Best,

                Hessam

                • 5. Re: If function, Aggregation, Filter, Fixed value
                  Don Wise

                  Hi Hessam,

                   

                  I tried a couple of things in the newly attached, and was able to extend out some new Dimensions based on the data to add more context (and possibility for a good filter) for your requirement inclusive of a stab at an LOD to exclude the necessary addresses across the entire domain, but didn't get anywhere with it. 

                   

                  I'm asking swaroop.gantela for some help on this one as it is similar to something else done a short while ago. Hopefully will weigh in on this...Thx, Don

                   

                  1st example.png

                  2nd example.png

                  3rd example.png

                  • 6. Re: If function, Aggregation, Filter, Fixed value
                    swaroop.gantela

                    Don,

                     

                    Thanks for the ping, interesting problem.

                     

                    Hessam,

                    I'm not sure if I fully caught the requirements.

                    Here is my understanding, please correct as needed:

                     

                    In the daterange of a datefilter, show the address if:

                    1) the address only has citations

                    2) the earliest citation in the time window comes before the earliest warning ever

                     

                    Exclude those that:

                    3) only have warnings

                    4) the first warning ever comes before the earliest citation in the window

                     

                     

                    The earliest warning ever for a given address is:

                    { FIXED [Address] : MIN( IF [Case]="Warning" THEN [Infraction Date] END ) }

                     

                    The earliest citation in the time window is:

                    WINDOW_MIN ( MIN ( IF [Case]="Citation" THEN [Infraction Date] END ) )

                     

                    Boolean Flag for earliest citation in window before earliest warning:

                    [Earliest Citation Window]<MIN([Earliest Warning Ever])

                     

                    So the filter for keeping or excluding is:

                    IF ISNULL ( MIN ( [Earliest Warning Ever] ) )    // has no warning at all

                    OR [Earliest Citation before Earliest Warning WINDOW]

                    THEN "Keep" ELSE "Exclude"

                    END

                     

                    Attached in the Forum thread is the workbook and a

                    smaller version of the datasource.

                     

                    Alternatively, if the only criterion is really that the address has never had a warning,

                    then can simply use:

                    { FIXED [Address]:MAX([Case])}="Citation" AND { FIXED [Address]:MIN([Case])}="Citation"

                    2 of 2 people found this helpful
                    • 7. Re: If function, Aggregation, Filter, Fixed value
                      Don Wise

                      Swaroop,

                       

                      Thank you! 

                       

                      It looks like I was close, but not close enough !! That LOD needed to be a FIXED, not EXCLUDE and also required to have an additional nested FIXED statement.  Great calc and I'll remember this one going forward.  Thanks, again!!

                       

                      Hessam,

                      Attached is what should be your final version (10.2) with a relative date filter added in. One worksheet showing detail by address and the other worksheet being a summary count by address.

                       

                      Swaroop's LOD works really well for what your'e trying to accomplish + he added some additional calcs in the thread in the event you wanted even more separation/detail.  So yeah, he went above and beyond!  I did have trouble downloading the workbook for some reason, so I've reattached it to this response.

                       

                      If you would, please mark Swaroop's response as CORRECT as he deserves the credit here.

                       

                      Thanks, Don

                      1 of 1 people found this helpful
                      • 8. Re: If function, Aggregation, Filter, Fixed value
                        Hessam Harandi

                        Dear Don and Swaroop,

                         

                        Thank you so much for all your work, these calculations looks very advance! I really appreciate your support.

                         

                        let me clear my question again,

                         

                        We are looking for the address's which received citation(ticket with an amount) without prior warning(ticket without an amount). The filter for date will help us to see the recent citations issued but it should keep the rule of showing only citations without any warning in any time span, not only for the filtered time window.

                        Therefore, if we use the filter and it shows us an address that received a citation ticket in the time window we selected and if same address had received a warning ticket before the time window we selected, this report is not what we are looking for.

                         

                        Any address shown on the table must have a history without any warning at any time.

                         

                        I am sorry that I couldn't describe my question clearly from the beginning.

                         

                        Thank you,

                         

                        Hessam

                        • 9. Re: If function, Aggregation, Filter, Fixed value
                          Don Wise

                          Hi Hessam,

                           

                          The most recent workbook that was attached does resolve your concern as best I can tell. 

                           

                          For example, in the below screenshot, the address of 22 Quincy Pl had a history of Warning in 2017 and multiple Citations in 2017 and 2018.

                          Please now note that If using the date filter in the most recent workbook, going back 7 years, 22 Quincy Pl is no longer in the view whatsoever as that address had a Warning in it's history.  Please see below screenshot from newest workbook:

                          Screen Shot 2018-04-27 at 9.06.52 AM.png

                          The new calc removes any address that has any history of a Warning and shows only addresses with Citations.  If you need to see just addresses only, simply remove all of the BLUE pills from the worksheet with exception of the address. 

                           

                          Does that address your concern "but it should keep the rule of showing only citations without any warning in any time span, not only for the filtered time window."? 

                           

                          Thanks!  Don

                          1 of 1 people found this helpful
                          • 10. Re: If function, Aggregation, Filter, Fixed value
                            Hessam Harandi

                            Dear Don,

                             

                            You are correct, it was my mistake! since I haven't sent you all of the data I was testing it with another address and I was confused.

                            It is working perfectly!

                            Thank you

                             

                            Hessam

                            • 12. Re: If function, Aggregation, Filter, Fixed value
                              swaroop.gantela

                              Don,

                               

                              Thanks for the methodical follow through.

                              Strong work, as always.

                               

                              Just for the sake of completeness,

                              here is the workbook I'd uploaded before,

                              but was having issues with rolling it back to an earlier version.