8 Replies Latest reply: Feb 15, 2016 5:33 AM by Naveen Booshi RSS

    two date ranges and active

    Naveen Booshi

      Hi Guys,

      I need to find the customers in two date ranges and have to validate if he/she is an active one as well.

       

      count({<StartDate={'>=$(vSysDate)<=$(vDropDeadLastWk)'}> + <StartDate={'>=$(vSysDate)<=$(vDropDeadLastWk)'}}> AND Status={'Active'}> GrantID)

       

      First the customers who are 'Active' and who fall in either of the buckets between two dates.

      I have to display only those cutomers.

      All dates are in DD/MM/YYYY format.

       

      Thanks

        • Re: two date ranges and active
          Sunny Talwar

          May be this:

           

          Count({<StartDate={"$(='>=' & vSysDate & '<=' & vDropDeadLastWk)"}, Status={'Active'}> GrantID)

           

          Where this -> ='>=' & vSysDate & '<=' & vDropDeadLastWk in a text object give you the format of your StartDate and range of where you want to filter the expression.

          • Re: two date ranges and active
            Massimo Grossi

            if you have 2 date range, try with (replace the variables vDate1, ....vDate4 with your vars)

             

            count(

                 {

                   <StartDate={">=$(=date(vDate1))<=$(=date(vDate2))"},Status={Active}>

                   +

                   <StartDate={">=$(=date(vDate3))<=$(=date(vDate4))"},Status={Active}>

                 }

              GrantID)

             

             

            maybe you don't need the date function

            • Re: two date ranges and active
              Hirish V

              Hi,

               

              You can use slider object for your custom selection between dates,

               

              Active Ranges-203583.PNG

               

              Create a slider object -> Put Date as a Field -> Mode Multi value and Value Mode discrete.

               

              This will help you to select range of two dates and According to that you can see customers and sales.

               

              Hope this Helps,

              PFA,

              HirisH

                • Re: two date ranges and active
                  Naveen Booshi

                  Hi Harish / M G/ Sunny,

                  The Customers are getting filtered between the dates but its picking all the other statuses, like Inactive,Closed etc.

                  I just need the Active ones between the date ranges with an OR between them.

                  Please help.

                    • Re: two date ranges and active
                      Hirish V

                      Hi,

                       

                      Dates is a different one ,After selecting the date ranges,

                       

                      The Report should be like this,to show only active customer names as below example:

                       

                      Straight Table:

                       

                      Dimension:

                      Customers

                       

                      Expression:

                      Sum({<Status={'Active'}>}Sales)

                       

                       

                      HTH,

                      Hirish

                      • Re: two date ranges and active
                        Sunny Talwar

                        Trying to understand here that do you have customers which can have different statuses like inactive, closed and active and you only want to show those customers which are active (and don't have inactive, closed) associated with them?

                         

                        Try this:

                         

                        Count({<StartDate={"$(='>=' & vSysDate & '<=' & vDropDeadLastWk)"}, Customer = e({<Status ={'Inactive', 'Closed'}>)>} GrantID)

                          • Re: two date ranges and active
                            Naveen Booshi

                            count(

                                 {

                                   <vSysDate={">=$(=date(vDropDeadMonth))<=$(=date(DropDeadDate))"},Status={Approved}>

                                   +

                                   <vSysDate={">=$(=date(vDropDeadLastWk))<=$(=date(DropDeadDate))"},Status={Approved}>

                                 }

                              GrantID)

                             

                            - This is returning just 1 for all the rows but my date ranges are very different.

                            Looking those GrantIDs between two sets of different dates.

                      • Re: two date ranges and active
                        Naveen Booshi

                        This works:

                         

                        if(

                        (($(vSysDate)>= $(vDropDeadMonth))

                        AND ($(vSysDate)<= $(vDropDeadDate))

                        AND Match( Status , 'Approved' , 'Pre Approved')

                        AND (Outcome=('Funded')

                        OR Outcome=('Phased Funded')

                        OR Outcome=('Conditionally Funded')

                        OR Outcome=('Partially Funded'))

                        )

                        OR

                        (($(vSysDate)>= $(vDropDeadLastWk))

                        AND ($(vSysDate)<= $(vDropDeadDate))

                        AND Match( Status , 'Approved' , 'Pre Approved')

                        AND Match( Outcome , 'Funded' , 'Phased Funded','Conditionally Funded','Partially Funded')

                        )

                        ,'Yes','No'

                        )