fill the missing values

[vpanchuda]

Hi

Struggling with missing /Null values 

Year	Month	ID	StartDate	EndDate	Level
2018	Jan	1	01-01-2018	02-01-2018	Excellent
2018	Feb	2	13-02-2018	14-02-2018	Excellent
2018	Aug	3	21-03-2018	22-03-2018	Good
2018	Sep	4	04-06-2018	05-06-2018	Excellent
2019	Jan	5	01-01-2019	02-01-2019	Good
2019	Jan	6	13-02-2019	14-02-2019	LOW
2019	Feb	7	21-03-2019	22-03-2019	Good
2019	Mar	8	04-06-2019	05-06-2019	LOW
2019	Sep	9	21-12-2019	22-12-2019	Excellent
2020	Feb	10	01-01-2020	02-01-2020	Excellent
2020	Mar	11	13-02-2020	14-02-2020	LOW
2020	May	12	21-03-2020	22-03-2020	Excellent

Script:

Fact:
LOAD
// "Year",
// "Month",
ID,
StartDate,
EndDate,
Level
FROM [lib:------
;

startdateCalander:

Load
CalanderDate as StartDate,
day(CalanderDate) as Day,
Week(CalanderDate) as Week,
Month(CalanderDate) as month,
Year(CalanderDate) as Year,
'Q'& Ceil(Month(CalanderDate)/3) as Quarter,
date(monthstart(CalanderDate),'MMM-YYYY')as MonthYear,

;
Load
Date(MinDate+IterNo()-1) as CalanderDate
while MinDate+IterNo()-1<=MaxDate
;

Load
num(Min(FieldValue('StartDate',Recno()))) as MinDate,
num(Max(FieldValue('StartDate',RecNo()))) as MaxDate
Autogenerate FieldValueCount('StartDate')

;

Present View  is as below because i believe because of null . User wants me to show the  YEAR field as '0' if there is no level in that particular Year in the below report values Level 'excellent' is available in 2018,2019 and 2020 hence it shows the count but Level'Good' there is no value in 2020 hence don't appear in the table similarly  for level 'LOW' there is no value in 2018 hence wont appear 

Expected view for Level Good    

Year	Count({<Level={'Good'}>}ID)
2018	1
2019	2
2020	0

Expected view for Level LOW

Year	Count({<Level={'LOW'}>}ID)
2018	0
2019	2
2020	1

[Sue_Macaluso]

@vpanchuda What product are you using? QlikView or Sense? I would like to move this into the correct product forum

[vpanchuda]

Something went wrong when i posted this :)...its Qlik sense