Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Is it possible with IF

Hello all,

Is it possible to write an IF in an expression like 

=IF(a<b, x = 1 AND IF(...) )

in my problem:

I have to check the date at first and if the date is smaller than a date in the future i want to set the status to YES and count the YESes.

 

I have a table with for example

 

Date1Status
15-07-2019NO
04-06-2019YES
10-08-2019NO
04-03-2019YES

 

I need to check if Date < Date(MonthEnd(Today(),+1)

If yes, i want to count the YESes. So instead of the output 2 (two YESes at first) i want as output 3.

Date1Status
15-07-2019YES
04-06-2019YES
10-08-2019NO
04-03-2019

YES

 

I need an IF expression like in another table:

=IF(Date(date#(Date1,'DD-MM-YYYY'),' DD.MM.YYYY')<DATE(MonthEnd(Today(),+1), 'DD.MM.YYYY'),Status='YES' AND 

IF(TestText='Total yes all ref', Count(If(Status='YES',Status))

 

Regards!

Labels (3)
2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

nr 1: pick(
Match(TestText,'Missing Due Date','Total yes all ref','A1 - NO','B1 - NO','C1 - NO','TOTAL')
,Count({<Duedate={''}>}Duedate)
,Count({<Status={'YES'}>}Status)
,Count({<Reference={'A1'},Status={'NO'}>}Status)
,Count({<Reference={'B1'},Status={'NO'}>}Status)
,Count({<Reference={'C1'},Status={'NO'}>}Status)
,Count({<Duedate-={''}>}Duedate)
)

 

the duedate field needs to be converted to a date or num field to create the setanalysis for duedate. When thats done you can use duedate={"<=$(=date(today()))"} or duedatenumber={"<=$(=floor(today()))"}

View solution in original post

Highlighted
Partner
Partner

you need to make sure the duedate field is a number/date to be able to check if it is in the future or not. When you have done that you can replace Status={'No'} with duedate={"<=$(=date(monthend(today())))"} or duedatenumber={"<=$(=floor(monthend(today())))"}

View solution in original post

5 Replies
Highlighted
Partner
Partner

use set analysis count({<Status={'YES'}>}Status)
Or count({<Date1={"<=$(=monthend(today()))"}>}Date1)
Highlighted
Creator III
Creator III

doesn't work for me...

i'll append the program for better understanding

 

Highlighted
Partner
Partner

nr 1: pick(
Match(TestText,'Missing Due Date','Total yes all ref','A1 - NO','B1 - NO','C1 - NO','TOTAL')
,Count({<Duedate={''}>}Duedate)
,Count({<Status={'YES'}>}Status)
,Count({<Reference={'A1'},Status={'NO'}>}Status)
,Count({<Reference={'B1'},Status={'NO'}>}Status)
,Count({<Reference={'C1'},Status={'NO'}>}Status)
,Count({<Duedate-={''}>}Duedate)
)

 

the duedate field needs to be converted to a date or num field to create the setanalysis for duedate. When thats done you can use duedate={"<=$(=date(today()))"} or duedatenumber={"<=$(=floor(today()))"}

View solution in original post

Highlighted
Creator III
Creator III

Match seems like a better option instead of TestText = '....'
Thank you!
I will rewrite expression one. But do you know how the do it with a future date like in expression two (31.07.2019) B1 NO, C1 NO A1 NO should counted as YES because their Duedate is smaller then 31.07.2019
Highlighted
Partner
Partner

you need to make sure the duedate field is a number/date to be able to check if it is in the future or not. When you have done that you can replace Status={'No'} with duedate={"<=$(=date(monthend(today())))"} or duedatenumber={"<=$(=floor(monthend(today())))"}

View solution in original post