Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Aggregation won't work - why?

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Instead of

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

Try to use

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

View solution in original post

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

Instead of

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

Try to use

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