topic Re: wrong aggr behavior for last day in month in App Development

wrong aggr behavior for last day in month

Tue, 16 May 2017 08:45:50 GMT

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

Re: wrong aggr behavior for last day in month

vinieme12 — Tue, 16 May 2017 09:28:17 GMT

Dimension

Date

Expression

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

Re: wrong aggr behavior for last day in month

vinieme12 — Tue, 16 May 2017 09:31:03 GMT

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)

Re: wrong aggr behavior for last day in month

Anonymous — Tue, 16 May 2017 09:44:34 GMT

The above approach will work try above one

Re: wrong aggr behavior for last day in month

Tue, 16 May 2017 09:51:20 GMT

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

Re: wrong aggr behavior for last day in month

vinieme12 — Tue, 16 May 2017 09:53:37 GMT

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

Re: wrong aggr behavior for last day in month

swuehl — Tue, 16 May 2017 09:53:47 GMT

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€)])

Re: wrong aggr behavior for last day in month

Tue, 16 May 2017 11:29:07 GMT

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);

Re: wrong aggr behavior for last day in month

Tue, 16 May 2017 14:09:49 GMT

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;

Re: wrong aggr behavior for last day in month

swuehl — Tue, 16 May 2017 15:37:02 GMT

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()?

Re: wrong aggr behavior for last day in month

Wed, 17 May 2017 09:51:25 GMT

Thanks Stefan,

In fact, I've already tried your proposed solution, but more simple with just the Floor(Monthend()) functions

The "if" didn't seem to work, even if I add the date() function to have the same formats.

T2:

load

rowno() as rownb,

text([Date]) as [DateTest], // ex: 15/01/2017

If([Date] = Floor(Monthend([Date])), [Date],'none') as MonthEndDate

resident T1;

Drop Table T1;

Neverthelless, the Date is correctly retrieved from the table T1.

Do you know why the "if" didn't work?

Re: wrong aggr behavior for last day in month

swuehl — Wed, 17 May 2017 09:56:22 GMT

Are your dates maybe also timestamps , just formatted as dates?

If(floor([Date]) = Floor(Monthend([Date])), Dayname([Date]),'none') as MonthEndDate

Re: wrong aggr behavior for last day in month

Fri, 19 May 2017 12:30:33 GMT

Thanks Stefan,

unfortunatelly, it doesn't work neither.

So temporary solution we've found for the moment is to list the last recorded date with an "if".

I've forgotten to mention that the last recorded day of each month is not always the least date of the month (see example after in march).

T2:

load

if([Date] = '31/1/2017' or [Date] = '28/2/2017' or [Date] = '29/3/2017', [Date] ) as MonthendDate

resident T1;

Drop Table T1;

Regards,

Nicolas

Re: wrong aggr behavior for last day in month

swuehl — Fri, 19 May 2017 12:37:14 GMT

It doesn't work even for the month of January or February?

Have you double checked that your dates are not just text literals? (follow the link to Henric's blog post)