I have the following requirement.
My Data table looks like below
I need to generate another Table wich looks similar to the one below
So, on upto the current Calender Week.
I have a Calender Table with all the weeks. My Raw data gives me only the Weeks that have some Sales value present. I need to Insert 0 as sales where there are actually none.
Is that week Direct field? Or it is calculating from Date field?
I would suggest trying the script below:
Num(Date#(Min(Period), 'YYYYMM'))as MinDate,
Num(Date#(Max(Period), 'YYYYMM')) as MaxDate
RESIDENT [Main Data];
//--- Assign the start and end dates to variables
LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');
DROP TABLE Temp_Calendar_Range; // Cleanup
Date(Temp_Date, 'YYYY/MM') as [Period]
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
This should generate the full rage of weeks between your Min and Max weeks.
After that you need to Left Join the Calendar to your Main table.
That will provide you with Null() values in weeks you're missing.
Your Solution was the closest to my problem. But, when in my case i have to Display all the weeks after selecting a Company ID.
Please, see the updated app.
Here when i select a Company ID i would still want the Table to show all the weeks.