Aggregation won't work - why?

[datanibbler]

Hi,

I have an Excel_file with a date_field - quite simple.

I want to aggregate that - to not have one record per day, but one per month, where the months should be fformatted like > June-14, Aug-14 < etc.

So I have the following in my LOAD

>> DATE(Datum_LF, 'MMM-YY') as Monat_eindeutig_LF <<

Then I have a RESIDENT LOAD where I just take the sum() of the costs (of all the days within that month) and I have a GROUP BY clause - I LOAD three fields from my first table, two cost_fields, both of which I sum up and that month_field - I don't take the date since that would mean grouping by day which doesn't make much sense.

<=> Still, after that, my table has just as many lines as before and about 28 instances of every value of the month_field - it was clearly not aggregated at all.

Can anybody tell me what I have done wrong?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

date() is only a formating function the values aren't changed only their visibility-format. You need more something like:

month(Datum_LF) & '-' & num(day(Datum_LF), '00') as Monat_eindeutig_LF

- Marcus

[MK_QSL · Accepted solution]

Instead of

DATE(Datum_LF, 'MMM-YY') as Monat_eindeutig_LF

Try to use

DATE(MonthStart(Datum_LF), 'MMM-YY') as Monat_eindeutig_LF