Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Are your dates maybe also timestamps , just formatted as dates?
If(floor([Date]) = Floor(Monthend([Date])), Dayname([Date]),'none') as MonthEndDate
See also
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
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)