Maximum date minus Minimum date

[#1]

Hello,

             i have a table like this

Entry no         Date

2                    25/11/2011

2                    30/11/2011

2                    5/12/2011

3                    24/11/2011

3                    28/11/2011

I want to get Max(Date)- Min(Date) for every entry number in a list box how to get it?

i have already tried aggr(Max(Date)-Min(Date),Entry no)

but its not working

any suggestions?

thanks

Harleen

[kaushiknsolanki · Accepted solution]

Hi,

     Try this in chart.

     Max(Total <EntryNo> Date) -  Min(Total <EntryNo> Date)

     For example refere the attached file.

Rehards,

Kaushik Solanki

[SunilChauhan]

load like below

tab1:

Load * inline [

Entry no    ,     Date

2         ,        25/11/2011

2        ,            30/11/2011

2        ,            5/12/2011

3       ,             24/11/2011

3       ,             28/11/2011

];

Load

makedate(subfield(Date,'/',3),subfield(Date,'/',2),subfield(Date,'/',1)) as Datenum,

Date,

[Entry no],

1 as flag

resident tab1;

drop table tab1;

and then

use this

Max(Datenum)-Min(Datenum)

inplace of  aggr(Max(Date)-Min(Date),Entry no)

[Miguel_Angel_Baeyens]

Hi Harleen,

Use the following script:

DataTemp:
LOAD * INLINE [
Entry no, Date
2, 25/11/2011
2, 30/11/2011
2, 05/12/2011
3, 24/11/2011
3, 28/11/2011
];
DataDiff:
LOAD "Entry no",
           Max(Date) - Min(Date) AS DateDiff 
RESIDENT DataTemp
GROUP BY "Entry no"
ORDER BY "Entry no", Date ASC;

This will perform faster, because you only need to use the field DateDiff in the listbox.

According to the script above, your code will work

=Aggr(Max(Date) - Min(Date), [Entry no])

Hope that helps.

Miguel

[swuehl]

Have you checked that your DateFormat is 'DD/MM/YYYY' and not 'MM/DD/YYYY' or something else?

[#6]

Thanks kaushik it worked perfectly in chart.

Miguel I wanted to apply this code in fron end not in the script.

your answer is correct .