Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Esteemed Contributor

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregation won't work - why?

Instead of

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

Try to use

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

2 Replies
MVP & Luminary
MVP & Luminary

Re: Aggregation won't work - why?

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

MVP
MVP

Re: Aggregation won't work - why?

Instead of

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

Try to use

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