Duration of Member visit

[#1]

Hi All ,

Again stuck with one challenging report requirement .

My client is asking me to generate "Duration of Member Visit" , which is nothing but Avg of days gapped between two consecutive bill dates .

I attached requirement . Please need help ASAP.

Regards,

Bhushan N

[#2]

Guys ,

Please help me out on this .

Bhushan N

[pdumas]

You want to calculate the duration between first and last visit, divided by the count of distinct visits-1 :

In a chart with MemberID as dimension :

Expressions :

count(distinct [BILL NUMBER]) : gives the total number of visit

min([Bill Date]) : gives the first visit

max([Bill Date]) : gives the last visit

(column(3)-column(2))/(column(1)-1) : gives what you need

Correct?

Pierre.

[#4]

Hi Pierre ,

A slight correction here .

What i need is something like this .

Average ( number of days lasped between 1st bill & 2nd bill , number of days lasped between 2nd bill & 3rd bill , number of days lasped between 3rd bill & 4th bill , ............ )

Pierre.

[pdumas]

You may try to do it, but the result will be the same

In your data, you have several transactions the same day. I would suggest you count [Bill Date] and not [BILL NUMBER] if your custemer issues more than one invoice for the same visit

Regards

[pdumas]

You may try to do it, but the result will be the same

In your data, you have several transactions the same day. I would suggest you count [Bill Date] and not [BILL NUMBER] if your custemer issues more than one invoice for the same visit

Regards

[#7]

Hi,

The best way is to create in the script

read you data....

LOAD
Id ,min(Date,2)-min(Date) as D1 /* nb of days between the 2 first */
,max(Date) -min(Date) as D2 /* nb of days between the first & the last */
,max(Date) -max(Date,2) as D3, /* the 2 last */

,min(Date,4) -min(Date,3) as D4, /* the 4th & the 3rd" */

...

RESIDENT Data
GROUP BY Id
;

jj