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
Dimension
Date
Expression
=
sum({<Date={"=Date=AGGR(DATE#(MonthEnd(Date),'DD/M/YYYY'),Date)"}>} Value)
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)
The above approach will work try above one
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
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
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€)])
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);
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;
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()?