Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

two date ranges and active

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

8 Replies

Re: two date ranges and active

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.

MVP
MVP

Re: two date ranges and active

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

hirishv7
Honored Contributor

Re: two date ranges and active

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

“Aspire to Inspire before we Expire!”
Not applicable

Re: two date ranges and active

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.

hirishv7
Honored Contributor

Re: two date ranges and active

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

“Aspire to Inspire before we Expire!”

Re: two date ranges and active

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)

Not applicable

Re: two date ranges and active

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.

Not applicable

Re: two date ranges and active

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'

)