two date ranges and active

[#1]

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

[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.

[maxgro]

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

[HirisH_V7]

Hi,

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

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

[#5]

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.

[HirisH_V7]

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

[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)

[#8]

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.

[#9]

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'

)