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: 
sunitha_chellaiah
Partner - Creator
Partner - Creator

Generating missing dates

Dear All,

I have created missing date using below logic. But while selecting another field, date values doesn't working properly.

Data:

link_DateEnquiry TypeCount
02-04-2020Field3
02-04-2020M2ALL1
03-04-2020M2ALL1
04-04-2020Online Portals-1
16-04-2020Field1
22-04-2020Walk-in1
23-04-2020Field2
26-04-2020M2ALL2
29-04-2020Telephone1
30-04-2020Field3
30-04-2020Telephone1
30-04-2020M2ALL0

 

Logic/script:

ETBR:
LOAD 

link_Date,
count,
"Enquiry Type"


FROM [lib://AttachedFiles/Retail Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

MinMaxDate:
Load Min (link_Date ) as MinDate ,
Max (link_Date ) as MaxDate
resident ETBR;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Join (ETBR)
Load Date(recno()+$(vMinDate)) as link_Date
Autogenerate vMaxDate - vMinDate;

 above script generates all the missing but while selecting 'Enquiry Type' field, missing dates are not coming, only values in the table are coming.

if select ''Enquiry Type'' = M2ALL then ,I want ouput  as mentioned below.

link_DateEnquiry Type NewSum([Count])
4/2/2020M2ALL1
4/3/2020M2ALL1
4/4/2020-0
4/5/2020-0
4/6/2020-0
4/7/2020-0
4/8/2020-0
4/9/2020-0
4/10/2020-0
4/11/2020-0
4/12/2020-0
4/13/2020-0
4/14/2020-0
4/15/2020-0
4/16/2020-0
4/17/2020-0
4/18/2020-0
4/19/2020-0
4/20/2020-0
4/21/2020-0
4/22/2020-0
4/23/2020-0
4/24/2020-0
4/25/2020-0
4/26/2020M2ALL2
4/27/2020-0
4/28/2020-0
4/29/2020-0
4/30/2020M2ALL0

 

Kindly help to solve this issue.

Thanks

3 Replies
Taoufiq_Zarra

what is the expected output if u select M2ALL  ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sunitha_chellaiah
Partner - Creator
Partner - Creator
Author

link_DateEnquiry Type NewSum([Count])
4/2/2020M2ALL1
4/3/2020M2ALL1
4/4/2020-0
4/5/2020-0
4/6/2020-0
4/7/2020-0
4/8/2020-0
4/9/2020-0
4/10/2020-0
4/11/2020-0
4/12/2020-0
4/13/2020-0
4/14/2020-0
4/15/2020-0
4/16/2020-0
4/17/2020-0
4/18/2020-0
4/19/2020-0
4/20/2020-0
4/21/2020-0
4/22/2020-0
4/23/2020-0
4/24/2020-0
4/25/2020-0
4/26/2020M2ALL2
4/27/2020-0
4/28/2020-0
4/29/2020-0
4/30/2020M2ALL0

 

This is my expected output if i select M2ALL

whiteymcaces
Partner - Creator
Partner - Creator

Two questions:

1. Have you confirmed that all those dates are now in the data?

2. Have you tried 'Include null values' option on the Dimensions and 'Include zero values' on the chart?