set analysis for date ranges

[#1]

Hi,

          I am writing a set analysis for data on policy count between some date range but not getting it working. Attached is the screen shot for the set analysis.

The Date function is getting a red underline.

Thanks

Lax

[jagan]

Hi,

Change your load script to

Directory;

LOAD POLICYNUMBER,

     POLICYSTATUS,

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

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

FROM

Test_Dates.xlsx

(ooxml, embedded labels, table is Sheet3);

It works for me with the expression.

=Count({<POLICYSTATUS={'Withdrawal'}, ELIGIBILITY_DATE={">=$(=MonthStart(vEDT))

<=$(=MonthEnd(vEDT))"}>} Distinct POLICYNUMBER)

And the result is 208.

Hope this helps you.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     I'm using personal edition i cann't open you file.If possible tel me about the dimension and expression in a image i will help you else jagan mohan will help you on this.

Celambarasan

[#4]

Hi,

     I think the exp. is ignoring the date condition. Actually there are only 27 policies with status Withdrawal and for Jan 2012. There are total 208 policies with status withdrawal.

Regards

Lax

[#5]

Hi, I have attached the image.

Thanks,

Lax

[CELAMBARASAN]

Hi,

     You have attached image. can you describe me about the problem by using the above expression in your qvw file with that image.

Celambarasan

[CELAMBARASAN]

Hi,

     Use the below expression.

     =Count({<POLICYSTATUS={'Withdrawal'},ELIGIBILITY_DATE={">=$(=TimeStamp(MonthStart(vEDT)))<=$(=TimeStamp(MonthEnd(vEDT)))"}>} POLICYNUMBER)

Hope it solves your problem

Celambarasan

[jagan]

Hi,

This expression will calculate the count between the monthstart and monthedn of vEDT date.  It will ignore the ELIGIBILITY_DATE.

=Count({<POLICYSTATUS={'Withdrawal'}, ELIGIBILITY_DATE={">=$(=MonthStart(vEDT))

<=$(=MonthEnd(vEDT))"}>} Distinct POLICYNUMBER)

Regards,

Jagan.

[#9]

Hi,

  I am now extracting the data with date as 'MM/DD/YYYY' . You can try the excel that I had attached with the sample application.

Thanks,

Lax

[#10]

HI, How can I equate the eligibility_date with extractiondate in the given set expression.  Actually I am trying to do the following.

For ex. If I plot on x axis year(eligibility_date) and on y axis

1. expression for count(policynumber) with certain status

2. expression for count(policynuber) with certain status and year(eligibility_date)=year(extractiondate)

  Similarly, I want to break that further into months.

Thanks,

Lax

[CELAMBARASAN]

Hi,

     try with this expression.

     =Sum(Aggr(if(Year(EXTRACTIONDATE)=Year(ELIGIBILITY_DATE),1,0),POLICYNUMBER))

Hope this is your need

Celambarasan