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

[#2]

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?

[swuehl]

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

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

See also

Why don’t my dates work?

[#4]

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

[swuehl]

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)