Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rsingh43
Creator
Creator

Group by date

Hi,

I am having some trouble trying to group fields by month/year. My end result is still bringing in day DDMMMYYYY but I only want the data grouped by MMMYYYY.

Data Load Editor:

Concatenate

LOAD Only(DATE([Created],'MMMYYY')) as StockDate2,

CustID,

Location,

    sum(TotalQty) as Receipted2,

    sum([Length])/2.54 as Length,

    sum([Width])/2.54 as Width,

    sum([Height])/2.54 as Height

Resident [L2-LocationInventory] group by CustID, Location, (DATE([Created],'MMMYYY'));


For this data I am using a table to display my results:

StockDate2CustIDLocationReceipted2LengthWidthHeight
Jan2018AUK20101520
Feb2018BUK50101015

Would be grateful for any assistance.

1 Solution

Accepted Solutions
rangam_s
Creator II
Creator II

Concatenate

LOAD text(DATE([Created],'MMMYYY')) as StockDate2,

CustID,

Location,

    sum(TotalQty) as Receipted2,

    sum([Length])/2.54 as Length,

    sum([Width])/2.54 as Width,

    sum([Height])/2.54 as Height

Resident [L2-LocationInventory] group by CustID, Location, text(DATE([Created],'MMMYYY'));


Try this,

Even if you covert the date using date format, Qlik will store original date as value so it will not work for aggregation (Like Excel only outside format will be changed but not value).

View solution in original post

5 Replies
mdmukramali
Specialist III
Specialist III

Hi,

Can you attach sample data file.

rsingh43
Creator
Creator
Author

Hi Mo,

I have attached an Excel file with sample data, is this OK?

mdmukramali
Specialist III
Specialist III

Hi,

Data:

LOAD Created,

MonthName(Created) as YearMonth,

    CustID,

    Location,

    Length,

    Width,

    Height

FROM

(ooxml, embedded labels, table is DATA);

LOAD

YearMonth,

CustID,

Location,

Sum(Length) as TotalLength,

Sum(Width) as TotalWidth,

Sum(Height) as TotalHeight

Resident Data

Group by YearMonth,CustID,Location;

DROP Table Data;



Kindly find the attached Sample Application.


rangam_s
Creator II
Creator II

Concatenate

LOAD text(DATE([Created],'MMMYYY')) as StockDate2,

CustID,

Location,

    sum(TotalQty) as Receipted2,

    sum([Length])/2.54 as Length,

    sum([Width])/2.54 as Width,

    sum([Height])/2.54 as Height

Resident [L2-LocationInventory] group by CustID, Location, text(DATE([Created],'MMMYYY'));


Try this,

Even if you covert the date using date format, Qlik will store original date as value so it will not work for aggregation (Like Excel only outside format will be changed but not value).

View solution in original post

rsingh43
Creator
Creator
Author

THANKS!