topic Re: Is it possible with IF in QlikView

Is it possible with IF

gf — Sat, 16 Nov 2024 03:19:43 GMT

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

Date1	Status
15-07-2019	NO
04-06-2019	YES
10-08-2019	NO
04-03-2019	YES

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.

Date1	Status
15-07-2019	YES
04-06-2019	YES
10-08-2019	NO
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!

Re: Is it possible with IF

bramkn — Wed, 12 Jun 2019 07:09:24 GMT

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

Re: Is it possible with IF

gf — Wed, 12 Jun 2019 07:34:30 GMT

doesn't work for me...

i'll append the program for better understanding

Re: Is it possible with IF

bramkn — Wed, 12 Jun 2019 07:46:59 GMT

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()))"}

Re: Is it possible with IF

gf — Wed, 12 Jun 2019 07:47:39 GMT

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

Re: Is it possible with IF

bramkn — Wed, 12 Jun 2019 07:51:19 GMT

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())))"}