Group by date

[Anonymous]

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:

StockDate2	CustID	Location	Receipted2	Length	Width	Height
Jan2018	A	UK	20	10	15	20
Feb2018	B	UK	50	10	10	15

Would be grateful for any assistance.

[mdmukramali]

Hi,

Can you attach sample data file.

[Anonymous]

Hi Mo,

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

[mdmukramali]

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 · Accepted solution]

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).

[Anonymous]

THANKS!