topic set analysis for date ranges in QlikView

set analysis for date ranges

Sat, 11 Feb 2012 08:30:55 GMT

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

set analysis for date ranges

CELAMBARASAN — Sat, 11 Feb 2012 08:52:09 GMT

Hi,

use like this,

Count({<POLICYCURRENTSTATUS={'Withdrawal'},ELIGIBILITY_DATE={">=$(=MonthStart(EXTRACTIONDATE))

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

Hope it helps

Celambarasan

set analysis for date ranges

Mon, 13 Feb 2012 12:40:26 GMT

Hi, How do I put a variable vEDT instead of EXTRACTIONDATE into this set analysis.

Thanks

Laxmikant

set analysis for date ranges

CELAMBARASAN — Mon, 13 Feb 2012 12:54:07 GMT

Hi,

Check with this,

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

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

If not working then tel whats you expression in variable?

Celambarasan

set analysis for date ranges

jagan — Mon, 13 Feb 2012 13:05:42 GMT

Hi,

Try this expression

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

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

Regards,

Jagan.

set analysis for date ranges

Tue, 14 Feb 2012 05:44:10 GMT

Hi, thanks for your input but both the scripts are failing, I still don't see any data. The variable is having expression as max(extractiondate). I am having enough data in this range but some how the script is not able to read.

I tried puttin single quotes as well but still same results.

Regards

Lax

set analysis for date ranges

jagan — Tue, 14 Feb 2012 06:24:33 GMT

Hi,

Can you attach the sample.

Regards,

Jagan.

Re: set analysis for date ranges

Tue, 14 Feb 2012 09:19:11 GMT

Hi, Please find the sample doc.

Regards,

Lax

Re: set analysis for date ranges

CELAMBARASAN — Tue, 14 Feb 2012 09:31:55 GMT

Hi,

Use your variable expression as =Max(EXTRACTIONDATE) instead of Max(EXTRACTIONDATE).

Celambarasan

Re: set analysis for date ranges

Tue, 14 Feb 2012 09:53:05 GMT

Hi,

it is =Max(EXTRACTIONDATE), still doesn't get it right. Can you attach the corrected doc.

set analysis for date ranges

jagan — Tue, 14 Feb 2012 10:03:30 GMT

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.

set analysis for date ranges

CELAMBARASAN — Tue, 14 Feb 2012 10:03:55 GMT

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

Re: set analysis for date ranges

Tue, 14 Feb 2012 11:49:42 GMT

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

Re: set analysis for date ranges

Tue, 14 Feb 2012 13:33:01 GMT

Hi, I have attached the image.

Thanks,

Lax

Re: set analysis for date ranges

CELAMBARASAN — Tue, 14 Feb 2012 13:56:47 GMT

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

Re: set analysis for date ranges

CELAMBARASAN — Tue, 14 Feb 2012 14:07:14 GMT

Hi,

Use the below expression.

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

Hope it solves your problem

Celambarasan

set analysis for date ranges

jagan — Tue, 14 Feb 2012 14:56:03 GMT

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.

set analysis for date ranges

Tue, 14 Feb 2012 14:57:12 GMT

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

Re: set analysis for date ranges

Tue, 14 Feb 2012 15:12:58 GMT

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

set analysis for date ranges

CELAMBARASAN — Wed, 15 Feb 2012 06:48:26 GMT

Hi,

try with this expression.

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

Hope this is your need

Celambarasan