Just in case it helps someone looking for similar solution.
i coult not find asoftable way to work as i need both weekly and monthly summary in my analysis.
as i said, solution provided by @sunny_talwar works well if there are no missing days, so I created a simple script to impute missing days;
1) Delete all tables (since i needed to run the script many times and with many intermediate tables, i was difficult to keep a track of which table is created when, so i simply delete all tables before data load to flush out all unnecessary tables)
LET vL.NumTabelle = NoOfTables();
FOR j=0 to $(vL.NumTabelle)-1
LET vL.nomeTabella = TableName(0);
DROP Table [$(vL.nomeTabella)];
NEXT j;
2) Load data from source
[Table1]:
LOAD
ID,
Date(Date#(DailyDate, 'DD/MM/YYYY') ) AS DailyDate,
Date(DateWeek, 'DD/MM/YYYY') ) AS [WC Week],
Value1,
Value2
FROM [lib://(Connection_name)/(file_name)]
(ooxml, embedded labels, table is Sheet1);
3) Get max and min value by each ID
[ALLID]:
LOAD
ID,
Min(Date(Date#(DailyDate, 'DD/MM/YYYY') )) AS MinDate,
// for records with only 1 dailyDate, keep max date as max date +1 for next step //
IF((Min(Date(Date#(DailyDate, 'DD/MM/YYYY') )))=(Max(Date(Date#(DailyDate, 'DD/MM/YYYY') ))),1,0)+Max(Date(Date#(DailyDate, 'DD/MM/YYYY') )) AS MaxDate
Resident [Table1]
Group by ID;
4) Iterate through each record and create copy of each record till max date is reached. For record with only 1 date, min date = max date (if not added one) and corresponding record will not get created, thats' why 1 is added if mindate and maxdate are same (which can only happen if there is only 1 record for a given ID)
this table is left joined on original table by ID and DailyDate
[ALLIDDates]:
Load ID,
Date(iterno()+MinDate) as DailyDate,
//Create Week New column here //
WeekStart(Date(iterno()+MinDate)) as [Week New]
resident [ALLID]
While iterno()+MinDate <= MaxDate;
left join
load * resident [Table1];
5) delete unnecessary tables
drop Table [ALLID];
drop table [Table1];
Exit Script;
this works perfectly well with Rangesum(above )formula that @sunny_talwar provided.
