Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
psk180590
Creator III
Creator III

Generate Missing Weeks

Hello All,

I have the following requirement.

My Data table looks like below

 

IDWeekSales
42949673792018/01264,31
2018/06

90,27

I need to generate another Table wich looks similar to the one below

 

IDWeekSales
42949673792018/01264,31
2018/020
2018/030
2018/040
2018/050
2018/0690,27
2018/420

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.

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Maybe like attached!

hope this helps

View solution in original post

13 Replies
Anil_Babu_Samineni

Is that week Direct field? Or it is calculating from Date field?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vardhancse
Specialist III
Specialist III

arvind_patil
Partner - Specialist III
Partner - Specialist III

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.

Creating master calendar

Thanks,

Arvind Patil

psk180590
Creator III
Creator III
Author

It is calculated from a Date field

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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.

Anil_Babu_Samineni

You need Master calendar for this, You can use simpler way provided by jagan‌ using Master Calendar Generation Script

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
psk180590
Creator III
Creator III
Author

Hi Anil,

I already have an Master Calender. How do i now generate the sales foe each Week or Date?

Frank_Hartmann
Master II
Master II

Did you uncheck "suppress zeros" on Presentation Tab?

Have a look at the attached sample

hope this helps

psk180590
Creator III
Creator III
Author

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.