Active customers over several months

[Anonymous]

Hi,

I have an "delivery from" datefield and "delivery to" datefield.  How can i sort out witch customers that have an delivery for over 3 months?

[antoniotiman]

Can You provide any sample data ?

[vishsaggi]

Can you share some sample example of your question. What exactly you want to check? Like you want to know the date difference between delilveryFrom to DeliveryTo is more than 3 months? Is this what you are looking for?  May be like in the script you can create a flag. I should know what exactly you are looking for?

[Anonymous]

sorry, dont have it here.

example:

Customer     Delivary from     Delivery to     x

aaa               01.01.2017     01.02.2017     x

bbb               01.01.2017     01.05.2017     x

ccc               01.02.2017     01.04.2017     x

ddd               01.01.2016     01.01.2099     x

eee               01.02.2016     01.01.2099     x

fff                  01.03.2017     01.01.2099     x

I need to know how many of them that where active for over 3 months.

Flags in script is a good way to start, whats the syntax?

[Anonymous]

se example above, just need to filter out those customers that was not active for over 3 months.

[vishsaggi · Accepted solution]

Your dates are going 2099. I am not sure if they are right. May be try this below, if not can you elaborate a little please...

LOAD *,

     DeliveryToNum - DeliveryFromNum AS DaysDifference,

     IF(DeliveryToNum - DeliveryFromNum > 90, 'Active','NotActive') AS [3MonthFlag];

LOAD *, Num(Floor(Date#(Deliveryfrom, 'DD.MM.YYYY'))) AS DeliveryFromNum,

        Num(Floor(Date#(Deliveryto, 'DD.MM.YYYY')))   AS DeliveryToNum INLINE [

Customer,     Deliveryfrom,     Deliveryto    

aaa             ,  01.01.2017 ,    01.02.2017    

bbb             ,  01.01.2017  ,   01.05.2017    

ccc             ,  01.02.2017  ,   01.04.2017    

ddd             ,  01.01.2016  ,   01.01.2099    

eee             ,  01.02.2016  ,   01.01.2099    

fff             ,  01.03.2017  ,   01.01.2099    

];

[Anonymous]

Perfect !

thanks 🙂