Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
Esteemed Contributor II

Re: wrong aggr behavior for last day in month

Dimension

Date

Expression

=

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

vinieme12
Esteemed Contributor II

Re: wrong aggr behavior for last day in month

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)

lakkydev
Contributor II

Re: wrong aggr behavior for last day in month

The above approach will work try above one

Not applicable

Re: wrong aggr behavior for last day in month

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
Esteemed Contributor II

Re: wrong aggr behavior for last day in month

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

MVP
MVP

Re: wrong aggr behavior for last day in month

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

Re: wrong aggr behavior for last day in month

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

Re: wrong aggr behavior for last day in month

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;

MVP
MVP

Re: wrong aggr behavior for last day in month

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

Community Browser