topic Re: Maximum date minus Minimum date in QlikView

Maximum date minus Minimum date

Wed, 11 Jan 2012 11:53:29 GMT

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

Re: Maximum date minus Minimum date

kaushiknsolanki — Wed, 11 Jan 2012 12:06:35 GMT

Hi,

Try this in chart.

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

For example refere the attached file.

Rehards,

Kaushik Solanki

Re: Maximum date minus Minimum date

SunilChauhan — Wed, 11 Jan 2012 12:08:21 GMT

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)

Re: Maximum date minus Minimum date

Miguel_Angel_Baeyens — Wed, 11 Jan 2012 12:31:06 GMT

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

Maximum date minus Minimum date

swuehl — Wed, 11 Jan 2012 13:22:49 GMT

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

Maximum date minus Minimum date

Thu, 12 Jan 2012 06:29:17 GMT

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 .