Missing Dates in the Calendar fields to be shown as Zero values

[SK28]

Hi,

I have a data as shown below

sys date	counts	ORG
w2	12	TCS
w3	1	TCS
w4	2	CTS
w7	4	CTS
w8	5	TCS
w10	6	CTS

I tried to map the date field with Calendar field and I have used it as dimension

Calendar field

Calendar date

W1

W2

W3

W4

W5

W6

W7

W8

W9

W10

basically 
when I select TCS the below output has to been shown but

Calendar date	Counts	org
W1	0	TCS
W2	12	TCS
W3	1	TCS
W4	0	TCS
W5	0	TCS
W6	0	TCS
W7	0	TCS
W8	5	TCS
W9	0	TCS
W10	0	TCS

basically Missing data has to be forced to be ZEROs, as shown above but I can see the data as below when they users are selecting the ORG as a filter

sys date	counts	ORG
w2	12	TCS
w3	1	TCS
w8	5	TCS

Can anyone help me on this, Missing dimension to be forced to zero

but we want to see

Can anyone help me on this?

[Jebrezov]

I’m interested in how others have done this. I too have a similar situation I’ve been thinking about. I haven’t tried it yet, but was thinking of adding a column for org to the master calendar and repeating all the dates for each org, then concatenate the org and date together to use as the master key to join the master calendar and raw data. This should ensure there is a row for every org and date combination which is where the issue lies.  Add in a coalesce statement on the count column to input a 0 if the value is null and I think it will give your desired output.

[SK28]

thanks for your response @Jebrezov 

Yup, even I have done in the above said way, doing a cross join from date related fields with ORG, but doing this the data size is becoming too big, Hence searching for an alternate solution

[Anil_Babu_Samineni]

We appreciate sharing sample QVW or QVF to work for you.

[SK28]

Appreciate @Anil_Babu_Samineni But it's not Possible,  and I shouldn't be doing it's Org data Please understand & thanks for your response...

if you can do this with the above mentioned data It would be Helpful! Apologies, Hope you understand

[SK28]

I have got till, showing all the Week Information, 
basically

Final:
load Org, dim2.. resident tablename 
join 
load date1,date2.. resident (calendar date)

this  gave me the Cross join of the table, so with all ORG's each date got associated.

Step 2:
In the main fact table,

ORG:
load Number, key , <other fields > resident Table_name
right join 
load ORG, Date , key , <other date information> resident final

dimensions are coming fine, But I'm Unable to force Count(Number) which has nulls to Zero

Count(number) + 0* Count({1}) 
even this not Forcing it to zero...

In the above example, If I select TCS
lets say there are 15 null records while counting measure in the line chart constant 15 

w1	15
w2	12
w3	1
w4	15
w5	15
w6	15
w7	15
w8	5
w9	15
w10	15

I have added this condition if(len(trim(number)) =0,0,number)

coalesce isn't working, Can anyone help me on this

[Jebrezov]

If it is null, i don’t  believe it will have a length.
Have you tried a set expression like the ones in the link below?

 https://community.qlik.com/t5/New-to-Qlik-Sense/Count-if-not-null/td-p/11644

[Ahidhar · Accepted solution]

do you want something like this

then try this

Let vmin=num(MakeDate(2023,11,01));
Let vmax=num(MakeDate(2023,11,10));

Date:
load
Date($(vmin)+rowno()-1,'DD/MM/YYYY') as Date
AutoGenerate $(vmax)-$(vmin)+1;

Calendar:
load
Date as Sys_Date
resident Date;
drop table Date;
Outer Join(Calendar)
ORG:
load * Inline
[ORG
TCS
CTS
];

left join(Calendar)
load * Inline
[Sys_Date,Counts,ORG
2/11/2023,12,TCS
3/11/2023,1,TCS
4/11/2023,2,CTS
7/11/2023,4,CTS
8/11/2023,5,TCS
10/11/2023,6,CTS
];
NoConcatenate
Calendar1:
load Sys_Date,
if(isnull(Counts),0,Counts) as counts,ORG
resident Calendar;
drop table Calendar;

[marcus_sommer]

This will be helpful: Generating Missing Data In QlikView - Qlik Community - 1491394