How to use Group By? my group by is not working!

[jim_chan]

Hi guys, 

i have such data in screenshot, and also attached on here(sample_data.xlsx), look at sheet2. 

End result, i want to sum it up group by store by date. but i failed to do so. please help a look in my scripts as well. sorry cant attached a QVF file. 

DATA:

EXPECTED RESULT, GROUP by Store by Date

 WHAT I GOT in Qlik Sense

and this is the script i used, please help, i dont know whwrre has gone wrong 😞

TEMP1:
LOAD
text(subfield (RETAIL_NAME ,'|', -1)) as RETAIL,
ORDER_TIME,
Date(Date#(ORDER_TIME,'DD MMM YYYY hh:mm'),'DD/MM/YYYY')AS ORD_DATE,
TRX_ID,
TRX_AMT
FROM [lib://AttachedFiles/sample_data1.xlsx]
(ooxml, embedded labels, table is Sheet2);

FINAL_TEMP:
LOAD
RETAIL,
ORD_DATE,
SUM(TRX_AMT) AS SUM_TRX_AMT
RESIDENT TEMP1
GROUP BY RETAIL, ORD_DATE;

DROP TABLE FINAL_TEMP;

exit script;

please help!

[Or · Accepted solution]

You never actually created a date column with the time trimmed. Try using DayName() or Floor() on your date column to remove the time aspect.

[brunobertels · Accepted solution]

Hi 

As far as i know 

this expression is the cause of the issue 

Date(Date#(ORDER_TIME,'DD MMM YYYY hh:mm'),'DD/MM/YYYY')AS ORD_DATE,

you need to FLOOR your date expression because here , even if not showing you still have a timestamp 

try then 

Date(floor(Timestamp#(ORDER_TIME,'DD MMM YYYY hh:mm')),'DD/MM/YYYY')AS ORD_DATE,

[jim_chan]

i seeeeeeeeeeeeeeeeeeeeeee... ok let me try with that !

[jim_chan]

oooooooh no.. ok. let me try it out Bruno!

[jim_chan]

Thanks finally got it!!!! thank you!