This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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.
Maybe like attached!
hope this helps
View solution in original post
Is that week Direct field? Or it is calculating from Date field?
How to generate weeks between two dates?
First check are you able to convert week into date or date field in your table then calculate master calender as below thread.
Creating master calendar
It is calculated from a 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.
You need Master calendar for this, You can use simpler way provided by jagan using Master Calendar Generation Script
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
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.