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: 
Anonymous
Not applicable

Active customers over several months

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?

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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    

];

Capture.PNG

View solution in original post

6 Replies
antoniotiman
Master III
Master III

Can You provide any sample data ?

vishsaggi
Champion III
Champion III

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
Not applicable
Author

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
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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    

];

Capture.PNG

Anonymous
Not applicable
Author

Perfect !

thanks 🙂