Is there a way to add missing date values in the script?

[that_anonymous_guy]

Hi All

I am working on a dataset where I have to deal with date fields. I have converted the date field using weekname so it gives week/year. The table is something like this:

Store	Target%	WeekYear	Duration	Planned
A	100	32/20	59	1.69
A	100	32/20	59	0
A	100	32/20	59	0
A	100	35/20	59	1.69
A	100	36/20	59	1.69
A	100	40/20	59	1.69
A	100	40/20	59	0
A	100	46/20	59	1.69
A	100	02/21	59	1.69
A	100	02/21	59	0

In this case, I only have planned start date and planned end date and using that I have calculated duration and I have to show this in a graph as a planned progress graph so I'm calculating planned like shown above 100/59 ie 1.69. The plan is to have WeekYear in x-axis and planned in y-axis. There is a date field which have the dates in which there was any sale and using that field I have already calculated the actual progress chart and now planned is required

This is the formula used to calculate planned progress

if(WeekYear=previous(WeekYear),0, target/planned))

The problem is that because few WeekYear fields are missing I'm not getting the correct data. For eg, if startdate is 22/20 and enddate is 31/20 then I need a continuous data of all the weeks in between so that the calculations can be done correctly.

I have tried using master calender but master calender is creating the missing dates in a new table but I need the missing fields in the same table so that the calculation can be performed correctly.

Is there a way to add new rows in the table which will have the missing WeekYears so the correct calculation can be performed or is there something that I'm missing about the master calenders. I'm pretty stuck with this for a while now and any leads would be appreciated.

Please let me know if any further clarification is required.

Thanks in advance 🙂

[aguirre]

I think you should have a look at Master Calendar

this way you get the missing dates or weeks

ex. 

Orders:
LOAD * INLINE [
OrderDate
1/1/2022
10/3/2023

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

taken from here: https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286

[hic · Accepted solution]

Take a look at https://community.qlik.com/t5/Design/How-to-populate-a-sparsely-populated-field/ba-p/1470637. 

This explains how to add missing date in a table.

[that_anonymous_guy]

@hic Thank you...that's exactly the kind of thing that I need. In my case I have table that have multiple columns other than Rates and I am trying to use inline peek for every columns to replace the missing values but it is behaving a bit differently. I hope I'm doing the right thing...haha...