Comparing 2 date fields

[krishnagutha1294]

Hi All

I have The data like below

Table1:
LOAD * INLINE [
    Product, order date, Delivery date, Status
    Pen, 01/01/2020, 05/01/2020, Deliverd
    Book, 12/01/2020, Null, NULL
    Paper, 22/02/2020, 20/02/2020, Deliverd
    Pencil, 05/05/2020, 10/05/2020, Deliverd
];

Now I want to calculate Count(Product) where Delivery date > Order date
Along with null values
That means count is 3(2 delivered and 1 Null)

Kindly can anyone help me how to resolve .

Thanks In Advance

[krishnagutha1294]

Please can anyone help me 

[Taoufiq_Zarra]

in load Scripit or UI ?

do you have String 'Null' to indicate Null value or '' ?

@krishnagutha1294 

[krishnagutha1294]

Both ways if possible

[Kushal_Chawda]

@krishnagutha1294  try below

Data:
LOAD *,
     if([Delivery date]>[order date] or (len(trim([Delivery date]))=0 or len(trim([order date]))=0),1,0) as Flag;
LOAD * INLINE [
    Product, order date, Delivery date, Status
    Pen, 01/01/2020, 05/01/2020, Deliverd
    Book, 12/01/2020, ,  
    Paper, 22/02/2020, 20/02/2020, Deliverd
    Pencil, 05/05/2020, 10/05/2020, Deliverd
];

Now you can below expression

=count(DISTINCT {<Flag={1}>}Product)

[krishnagutha1294]

Thanks its working.

But i need to create a table with the outputs if i use that field names its showing all records.Please can you help me.

[Kushal_Chawda]

@krishnagutha1294  Sorry I am not following that. Can you give example?

[krishnagutha1294]

Output Like below attached data

[krishnagutha1294]

Please anyone can help me how to resolve above mention problem.

Thanks in advance 

[OmarBenSalem]

Step 1 : add a new delivery date for you null values :

LOAD *,

if(len(trim("Delivery date"))=0,date(today()),"Delivery date") as "Delivery date_ForCalculation"
INLINE [
Product, order date, Delivery date, Status
Pen, 01/01/2020, 05/01/2020, Deliverd
Book, 12/01/2020, ,
Paper, 22/02/2020, 20/02/2020, Deliverd
Pencil, 05/05/2020, 10/05/2020, Deliverd
];

Step 2 :

- to know the nb of products : 

count( distinct{<Product={"=[Delivery date_ForCalculation]>[order date]"}>}Product)

- To know the products name (in a text or kpi) :

Concat(distinct{<Product={"=[Delivery date_ForCalculation]>[order date]"}>}Product,', ')

- to show them in a table : replace product field by : 

=aggr(only({<Product={"=[Delivery date_ForCalculation]>[order date]"}>}Product),Product)

and uncheck show null values

final result :