topic Re: two date ranges and active in QlikView

two date ranges and active

Fri, 29 Jan 2016 16:09:40 GMT

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 — Fri, 29 Jan 2016 18:15:23 GMT

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

maxgro — Sat, 30 Jan 2016 13:23:50 GMT

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_V7 — Sat, 30 Jan 2016 17:13:29 GMT

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

Re: two date ranges and active

Mon, 01 Feb 2016 09:36:19 GMT

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_V7 — Mon, 01 Feb 2016 11:10:44 GMT

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 — Mon, 01 Feb 2016 11:11:54 GMT

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

Wed, 10 Feb 2016 16:00:58 GMT

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

Mon, 15 Feb 2016 10:33:30 GMT

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

)

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

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

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

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

)

,'Yes','No'

)