topic Re: Generating missing dates in App Development

Generating missing dates

sunitha_chellaiah — Wed, 03 Jun 2020 10:56:06 GMT

Dear All,

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

Data:

link_Date	Enquiry Type	Count
02-04-2020	Field	3
02-04-2020	M2ALL	1
03-04-2020	M2ALL	1
04-04-2020	Online Portals	-1
16-04-2020	Field	1
22-04-2020	Walk-in	1
23-04-2020	Field	2
26-04-2020	M2ALL	2
29-04-2020	Telephone	1
30-04-2020	Field	3
30-04-2020	Telephone	1
30-04-2020	M2ALL	0

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_Date	Enquiry Type New	Sum([Count])
4/2/2020	M2ALL	1
4/3/2020	M2ALL	1
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/2020	M2ALL	2
4/27/2020	-	0
4/28/2020	-	0
4/29/2020	-	0
4/30/2020	M2ALL	0

Kindly help to solve this issue.

Thanks

Re: Generating missing dates

Taoufiq_Zarra — Wed, 03 Jun 2020 11:22:46 GMT

what is the expected output if u select M2ALL ?

Re: Generating missing dates

sunitha_chellaiah — Wed, 03 Jun 2020 12:36:44 GMT

link_Date	Enquiry Type New	Sum([Count])
4/2/2020	M2ALL	1
4/3/2020	M2ALL	1
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/2020	M2ALL	2
4/27/2020	-	0
4/28/2020	-	0
4/29/2020	-	0
4/30/2020	M2ALL	0

This is my expected output if i select M2ALL

Re: Generating missing dates

whiteymcaces — Wed, 03 Jun 2020 12:58:40 GMT

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?