topic Re: Count based on date range in QlikView

Count based on date range

Tue, 10 Dec 2013 01:09:04 GMT

Hi All,

I am having dataset

ID	Date
A	15/10/2013
B	14/10/2013
C	2/9/2013
D	4/5/2012
E	7/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)

Re: Count based on date range

jagan — Tue, 10 Dec 2013 03:10:24 GMT

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.

Re: Count based on date range

Tue, 10 Dec 2013 04:32:49 GMT

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)

Re: Count based on date range

senpradip007 — Tue, 10 Dec 2013 04:48:51 GMT

try this

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

Hope it will help.

Re: Re: Count based on date range

jagan — Tue, 10 Dec 2013 05:27:39 GMT

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.

Re: Count based on date range

brijesh1991 — Tue, 10 Dec 2013 05:33:42 GMT

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

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

Re: Count based on date range

Anonymous — Tue, 10 Dec 2013 06:40:47 GMT

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

Re: Count based on date range

Anonymous — Tue, 10 Dec 2013 07:14:37 GMT

Please check with latest that i edited.

Re: Count based on date range

Wed, 11 Dec 2013 02:24:19 GMT

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)

Re: Count based on date range

Fri, 13 Dec 2013 01:15:49 GMT

Thanks it is working fine