Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
what is the expected output if u select M2ALL ?
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
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?