Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have the following requirement.
My Data table looks like below
ID | Week | Sales |
4294967379 | 2018/01 | 264,31 |
2018/06 | 90,27 |
I need to generate another Table wich looks similar to the one below
ID | Week | Sales |
4294967379 | 2018/01 | 264,31 |
2018/02 | 0 | |
2018/03 | 0 | |
2018/04 | 0 | |
2018/05 | 0 | |
2018/06 | 90,27 |
2018/42 | 0 |
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.
Thanks
Sai.
Is that week Direct field? Or it is calculating from Date field?
Hi Pakalapati,
First check are you able to convert week into date or date field in your table then calculate master calender as below thread.
Thanks,
Arvind Patil
It is calculated from a Date field
I would suggest trying the script below:
Temp_Calendar_Range:
LOAD
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
Calendar:
LOAD DISTINCT
Date(Temp_Date, 'YYYY/MM') as [Period]
;
LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
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.
You need Master calendar for this, You can use simpler way provided by jagan using Master Calendar Generation Script
Hi Anil,
I already have an Master Calender. How do i now generate the sales foe each Week or Date?
Did you uncheck "suppress zeros" on Presentation Tab?
Have a look at the attached sample
hope this helps
Hello Frank,
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.