Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Count based on date range

Hi All,

I am having dataset

IDDate
A

15/10/2013

B14/10/2013
C2/9/2013
D

4/5/2012

E7/6/2012

I need the count of id's if it coming in last 15 months from today

For eg in this case my output will be count(ID) = 3 (i.e. A ,B and C)

1 Solution

Accepted Solutions
senpradip007
Valued Contributor III

Re: Count based on date range

try this

 

Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)

Hope it will help.

9 Replies
MVP
MVP

Re: Count based on date range

Hi,

Try this expreesions

For last 15 months

=Sum({<Date={'>=$(=AddMonths(Max(Date), -15))<=$(=Date(Max(Date)))'}>} ID)

Before doing this please check the date formats are correct in Date dimension and =AddMonths(Max(Date), -15), if not format the date like below

=Sum({<Date={">=$(=Date(AddMonths(Max(Date), -15), 'D/M/YYYY')<=$(=Date(Max(Date), 'D/M/YYYY'))"}>} ID)

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Count based on date range

Hi Jagan,

Thanks for your reply.

I tried this syntax but it is giving me blank

=count({<[Service Header.Completion Date]={">=$(=Date(AddMonths(Max([Service Header.Completion Date]), -15),'D/M/YYYY')<=$(=Date(today(), 'D/M/YYYY'))"}>} Key_VehicleRetention)

senpradip007
Valued Contributor III

Re: Count based on date range

try this

 

Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)

Hope it will help.

MVP
MVP

Re: Re: Count based on date range

Hi,

Try this expression

=Count({<Date={">=$(=Date(AddMonths(Today(), -15), 'D/M/YYYY'))<=$(=Date(Today(), 'D/M/YYYY'))"}>} ID)

Also please find attached file it is working there.

Regards,

Jagan.

brijesh1991
Valued Contributor

Re: Count based on date range

Hey use this expression; it's working in my dashboard.

Count({<Date ={">=$(=MonthStart(max(Date),-14))<=$(=max(Date))"},Year=,month=,Quarter=>}ID)

anantmaxx
Contributor III

Re: Count based on date range

Hi,

in your script do this

ID,

Date(Date,'DD/MM/YYYY') AS Date

from abc<>;

then use expression

=Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)

you will get answer 3

Regards,

Anant

anantmaxx
Contributor III

Re: Count based on date range

Please check with latest that i edited.

Not applicable

Re: Count based on date range

First, please create the flag in the table like below:

Let vBaseDate = Num(AddMonths(Today(), -15));

LOAD *,

          IF(Floor([Completion Date]) > = $(vBaseDate) , 1 ,0) AS 15M_FLAG;

On the UI, please use the expression:  COUNT({<15M_FLAG={1}>} Key_VehicleRetention)

Not applicable

Re: Count based on date range

Thanks it is working fine

Community Browser