Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

wrong aggr behavior for last day in month

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:

DateValue
3/1/20175
3/1/20176
10/1/20177
10/1/20175
10/1/20172
17/1/20179
24/1/20178
31/1/20179
31/1/20179
7/2/20172
21/2/20176
28/2/201720

Here is the expected output

DateValue
31/1/201718
28/2/201720

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

DateValue
31/1/201760 (wrong)
28/2/201728 (wrong)

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

Many thanks in advance,

Nicolas

13 Replies
vinieme12
Champion III
Champion III

Dimension

Date

Expression

=

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

The above approach will work try above one

Not applicable
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

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