Date group by is not working.

[Vishal]

Hello Everyone, 

Please refer to below table, wherein I have 'SHIP_DATE' and 'QYT_DISPENSED', and the column Z i.e. 'week_date' I have created using the code as - date(if(WeekDay(SHIP_DATE,0)=4,SHIP_DATE, WeekEnd(SHIP_DATE,0,4)+1)) as week_date - which is basically giving Friday if the date is Friday, otherwise round off the date to the next Friday. 

Now, I need to replicate the below SAS code into Qlikview - 

I have used the below code - 

However, the result is not as I wanted. It may seem trivial to some but I am really stuck here. Kindly help. The result I am getting is - 

It is supposed to show 04-22-16 = 16 or 04-15-16 = 6. It is not grouping the dates as its supposed to. The problem I have figured is that it is grouping the 'QYT_DISPENSED' of actual Fridays separately than the dates I have converted to Friday. See below - 

Please help! Thanks in advance.

[BrunPierre · Accepted solution]

Maybe this;

[Temp_interim.nps_starter_pack];
NoConcatenate
LOAD DATE(DATE#(week_date,'DD-MM-YYYY'),'MM-DD-YYYY') as weekdate,
QTY_DISPENSED as NPS,
'SD_starter_pack_NPS' as Flag

resident interim.SD_final;
Where wildmatch(NDC_NBR,'000123456') and not wildmatch(ACCOUNT_NAME,'*BLOCKED*');

NoConcatenate
interim.nps_starter_pack:
LOAD weekdate,
Flag,
SUM(NPS) as NPS,

resident [Temp_interim.nps_starter_pack]
Group by weekdate,Flag;
Order by weekdate,Flag;
Drop table [Temp_interim.nps_starter_pack];

[Vishal]

Thanks  @BrunPierre , It worked! 

Some syntax here and there, and Group by and Order by would not work together. But, I got the idea. Thanks again!