Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
marius_larsen
Contributor II

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
Highlighted
vishsaggi
Esteemed Contributor III

Re: Active customers over several months

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
Highlighted
antoniotiman
Honored Contributor III

Re: Active customers over several months

Can You provide any sample data ?

Highlighted
vishsaggi
Esteemed Contributor III

Re: Active customers over several months

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?

Highlighted
marius_larsen
Contributor II

Re: Active customers over several months

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?

Highlighted
marius_larsen
Contributor II

Re: Active customers over several months

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

Highlighted
vishsaggi
Esteemed Contributor III

Re: Active customers over several months

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

Highlighted
marius_larsen
Contributor II

Re: Active customers over several months

Perfect !

thanks 🙂