wrong aggr behavior for last day in month

[#1]

Hi,

I have a problem with the aggr function: it doesn't sum the values from the last day of each month.

Here is the data set:

Date	Value
3/1/2017	5
3/1/2017	6
10/1/2017	7
10/1/2017	5
10/1/2017	2
17/1/2017	9
24/1/2017	8
31/1/2017	9
31/1/2017	9
7/2/2017	2
21/2/2017	6
28/2/2017	20

Here is the expected output

Date	Value
31/1/2017	18
28/2/2017	20

The following solution takes the right dates but sum all the month values:

Dimension =aggr(Max(Date),YearMonth)

Measure = sum({$<Date= {"=aggr(Max(Date),YearMonth)"}>} [Planned value (K€)])

Date	Value
31/1/2017	60 (wrong)
28/2/2017	28 (wrong)

Any idea to have only the sum of the last day? (without valuelist if possible)

Many thanks in advance,

Nicolas

[vinieme12]

Dimension

Date

Expression

=

sum({<Date={"=Date=AGGR(DATE#(MonthEnd(Date),'DD/M/YYYY'),Date)"}>} Value)

[vinieme12]

Another Approach is to create a RowKEY() that identifies each row separately

LOAD Date,

     Value,

     RowNo() as RowKEY

Then

Dimension

Date

Expression

=

sum({<RowKEY={"=Date=DATE#(MonthEnd(Date),'DD/M/YYYY')"}>} Value)

[Anonymous]

The above approach will work try above one

[#5]

Actually, the Date was generated artificially retrieving the file name during the LOAD process.

I guess I have to add the last day date after the first LOAD process, right?

Sorry, this is quite complex

[vinieme12]

if the sample you've posted closely represents your actual dataset then the methods and expressions i provided above will work

or else please post a sample app with expected output

[swuehl]

If you want to use advanced search (which groups by your field and expects a boolean answer to the question if the field value should be included or not):

Measure

= sum({$<Date= {"=Date = Floor(Monthend(Date))"}>} [Planned value (K€)])

Probably easier would be to use a master calendar and flag the monthend dates in your script (using the same logic).

LOAD

     Date,

     If(Date = Floor(Monthend(Date)),1,0) as MonthendFlag,

     ...

Measure

= sum({$<MonthendFlag= {1} >} [Planned value (K€)])

[#8]

Thanks Vineeth,

here is the code. As the Date is generated progressively with several files including the date information, we cannot define the MonthEnd(Date):

for each File in filelist (path_Pallet)

week_counter = $(week_counter) +1

[DTC]:

LOAD

  date(mid(filename(),26,10)) as [Date],

    text(date(mid(filename(),26,10),'YYYY/MM')) as YearMonth,

    $(week_counter) as CW,

    *

FROM $(File)

(ooxml , embedded labels , table is OPX2);

[#9]

Thanks Stefan,

Actually, the MonthendFlag column has "0" in all the fields.

Do you have another solution?

Note: the MonthendFlag column has been created afterwards, complete code:

for each File in filelist (path_Pallet)

week_counter = $(week_counter) +1

T1:

LOAD

  date(mid(filename(),26,10)) as [Date],

    text(date(mid(filename(),26,10),'YYYY/MM')) as YearMonth,

    $(week_counter) as CW,

    *

FROM $(File)

(ooxml , embedded labels , table is OPX2);

T2:

load

     *,

     If(date([Date]) = date(Monthend([Date])),[Date]) as MonthEndDate

resident T1;

Drop Table T1;

[swuehl]

That's because Monthend() returns a timestamp, corresponding to the last millisecond of the month.

Noticed that I've used a different expression using Floor()?