topic Re: Help with Minimum for Field ignoring selection in QlikView

Help with Minimum for Field ignoring selection

Tue, 15 Apr 2014 17:21:22 GMT

Hi Qlikview Experts,

Really appreciate your help in advance - been struggling with this for quite some time and it I may just be approaching this the wrong way.

I need to find out the Min date associated with a Field (ID) while Ignoring the Selection Currently In Place.

Example

Current Selection: Load File Field = 'Week of April 15.xls'

Resulting Straight Table:

Load Date	Load File	ID Number	Desired First Load Date
15-Apr	Week of April 15.xls	ABC-123	1-Apr
15-Apr	Week of April 15.xls	DDD-444	14-Apr
14-Apr	Week of April 15.xls	DDD-444	14-Apr

Original Set of Data

Load Date	Load File	ID Number
15-Apr	Week of April 15.xls	ABC-123
1-Apr	Week of April 1.xls	ABC-123
4-Apr	Week of April 1.xls	ABC-123
15-Apr	Week of April 15.xls	DDD-444
14-Apr	Week of April 15.xls	DDD-444
7-Apr	Week of April 7.xls	EEE-000

The 'First Load Date' is the field I'm struggling with. This should return the First Load Date for each ID Number. What I've tried already:

=aggr(min( {1} [Load Date], [ID Number]) -- this returns 1-Apr for everything (which makes sense)

=min([Load Date]) - this returns the minimum for the ID Number of the current selection (In the example above it would return 15-Apr for ABC-123 and Apr-14 for DDD-444)

Would appreciate your thoughts, I always struggle communicating these problems.

Re: Help with Minimum for Field ignoring selection

Tue, 15 Apr 2014 17:58:25 GMT

May you share sample. It is always easier to fight having model and sample data ....

Re: Re: Help with Minimum for Field ignoring selection

Tue, 15 Apr 2014 19:03:25 GMT

Thanks for the quick response Dariusz, please see the attached file.

Re: Re: Re: Help with Minimum for Field ignoring selection

swuehl — Tue, 15 Apr 2014 19:49:56 GMT

Maybe like this?

=aggr(min({1} Total<IDNumber> LoadDate),IDNumber,LoadDate)

Re: Help with Minimum for Field ignoring selection

Tue, 15 Apr 2014 19:57:00 GMT

Amazing! This site never ceases to wow me.

Hopefully someone else down the line will have the same question.

Appreciate everyones effort (especially you swuehl)