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

Excluding field values in script

Hi All

I have a table which contains Status,Operational Date,Cancellation Date.In Status column some sites are operational and some are disconnected,also some sites which have cancellation date in current month are also shown Disconnected.Now I am excluding Disconnected sites in script itself by giving where Status<>'Disconnected',but,I need values from sites whose status is disconnected but cancellation date is of current month.Please help me in solving this issue.Thank you in advance

13 Replies
Anonymous
Not applicable
Author

Hi Tresesco

Now that I understood how to write Expression,but,it is not giving values of sites which are disconnected in November and December when I am deriving values form that fields.Please help.how can I modify above expression so that I can get values for Nov and Dec fields.I think i need to use ADDYEARS function but not sure how to do

Anonymous
Not applicable
Author

is it something like

Where  Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

  not (Status='Disconnected' and (month(AddMonths(today(),-1))<>Month([Cancellation Date])) and (Year(AddYears(Today(),-1))<>Year([Cancellation Date])))

Not applicable
Author

Hey Vani,

where not isnull(Country) and  Country<>'xyz' and len(trim([Account Number]) > 0)  and not (match(Status,'Disconnected') and (month(AddMonths(today(),-1))<>Month([Cancellation Date]))

and (Year(AddYears(Today(),-1))<>Year([Cancellation Date]))))

Anonymous
Not applicable
Author

I have tried this expression and it is working fine.Thank you all for your help.

           Where  Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

(Status='Operational' or (Status='Disconnected'  and

([Cancellation Date]>=MonthStart(Today(),-3) and [Cancellation Date]<=MonthEnd(Today(),0) and

([Cancellation Date]>=YearStart(Today(),-1) and [Cancellation Date]<=YearEnd(Today(),0))))) ;