How to remove duplicate lines from AGGR

[tinkerz1]

Hi,

I am using the AGGR function to group on selected items but I am getting duplications on rows, how can I use AGGR to group on a date range, max() and a particular field item?

The code below is intended to bring back the MAX download Month Year Item per date range and where the issue is closed.

But I get 2 rows returned.

The data is uploaded once a month and will have duplicate IDs but the status will change according the status of the issue.

Thanks,

=aggr(max([Download Month-Year Item]) and [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

Issue - Location L2	Issue - ID	Issue - Closed Date	Issue - Owner	Issue - Status	Download Month-Year Item	Max Download Month Year Item
Europe	1	16-Dec-15	Mr A	Closed	01-Feb-16	-1
Europe	1	16-Dec-15	Mr A	Closed	01-Dec-15	-1
Europe	2	30-Sep-15	Mr B	Closed	01-Nov-15	-1
Europe	2	30-Sep-15	Mr B	Closed	01-Sep-15	-1

[Chanty4u]

=aggr(Distinct max([Download Month-Year Item]) and [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

or

aggr(max([Download Month-Year Item]) and    Distinct [Issue - Status]='Closed' and date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),[Issue - ID],[Issue - Closed Date])

[sunny_talwar]

I am not completely sure I understand what you are trying to do. Do you have a sample you can share?

[hic]

You use

   aggr(

      max([Download Month-Year Item]) and

         [Issue - Status]='Closed' and

         date([Issue - Closed Date])>=addmonths(date(vMEEndate),-12),

      [Issue - ID],

      [Issue - Closed Date]

       )

The first parameter of the Aggr should be an aggregation, but you instead have a Boolean expression that only will return -1 or 0. Further, since you use naked field references, you may get undesired results.

Secondly, you say that you want one value per DateRange. But your Aggr() has both [Issue - ID] and [Issue - Closed Date] as dimensions. Then you will get one value per combination of these two, i.e. several per DateRange.

Thirdly, you have not wrapped the Aggr in an aggregation function. You should probably use a chart with DateRange as dimension and Max(Aggr(...)) as measure.

See also

Pitfalls of the Aggr function

Use Aggregation Functions!

HIC

[swuehl]

Try

Max(

{<  [Issue - Status]={'Closed'}, [Issue - Closed Date] = {">=$(=Date(addmonths(vMEEndate,-12),'DD-MMM-YY'))"}>}[Download Month-Year Item])

as expression in your chart with dimension Download MonthYear item removed.