Hi,
I need to write an "IF" query that has more than just one "IF" and dont know how and cant find an example, maybe it isnt possible in JET?? I need to say =IF field A is =1 then…, if field A is = 2 then…, if field A is =3 then…. else…
the only "IF" function I can use succesfully is the =IF(logical test, value if true, value if false), how can I use this with multiple IF's?
thanks,
K
Date
Votes
8 comments
-
Jet Reports Historic Posts Official comment Well an example might look like this:
=IF($G9<=$B$4,"01/01/2012..02/01/2012",IF(AND($G9>=DATE(2012,1,1),$G9<DATE(2012,2,1)),"..02/01/2012","01/01/1900"))
Then you reference the result of that for your posting date filter in the NL(Sum) function. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi K,
What exactly are you trying to do? If field A = 1 then what? Are there some other filters you want to set in that case or something else?
Regards,
Hughes -
Jet Reports Historic Posts well…, here is what I am working on currently and though I know it isnt right it should show what I am tyring to achieve, I think? Basically "G9" is the First Revenue Date(FRD) and if the FRD falls between 01/01/2012 and 02/01/2012 then sum all postings since the begging of time to the end date 02/01/2012 otherwise if the FRD is < OR = 02/01/2012 then sum only postings within the date range 01/01/2012 through 02/01/2012. If the FRD falls after the date range end it is "0" or blank. Clear as mud? Hopefully this makes sense somewhat?
=IF($G9<=$B$4,NL("Sum","Shipment Ledger Entry","Amount","Shipment No.",$F9,"Posting Date","01/01/2012..02/01/2012","Company=","30-International","Posting Type","Expense","Posting Type 2", "''","Exclude from Rev/Exp","False"),IF($G9 = "01/01/2012..02/01/2012",NL("Sum","Shipment Ledger Entry","Amount","Shipment No.",$F9,"Posting Date","<=02/01/2012","Company=","30-International","Posting Type","Expense","Posting type 2","''","Exclude from Rev/Exp","False")),"''") -
Jet Reports Historic Posts So it seems like instead of embedding the entire NL(Sum) formula in your Excel IF function, you could just return the posting date filter. Then you just have 1 NL(Sum) function that references the result of the IF functions. You can embed multiple Excel IF functions inside each other up to I think 7 or so embedded functions, so I think you can cover all your cases just by embedding IF functions inside one another as you are already doing. In terms of making the NL(Sum) value 0, you could just specify a date filter that you know will not yield any shipment ledger entries (such as "..1/1/1950" or something like that). Does this help?
Regards,
Hughes -
Jet Reports Historic Posts sorry I dont really follow. I will keep plugging away at it, I am bound to get it eventually right? If you can post an example of your suggestion it would speak volumes. thanks!
-
Jet Reports Historic Posts This worked great Hughes, thanks. Just out of curiosity why the different date formats (01/01/2012..02/01/2012 OR 2012,1,1 and 2012,2,1) ??
-
Jet Reports Historic Posts So first you have to understand that this entire problem is an Excel problem, not a Jet problem. Jet isn't doing anything with that Excel IF function; Jet just gets the resulting ranges. In order to do comparisons like > or < with dates in Excel, you need Excel to know that it's a date. Excel doesn't understand what "01/01/2012" means; that's just a string to Excel. So you have to use the Excel DATE formula to turn it into a date in Excel. The Excel DATE formula takes 3 arguments: YEAR, MONTH, DAY, which is what I'm passing in. Does that make sense?
Regards,
Hughes -
Jet Reports Historic Posts yes, thank you for the info. Have a great weekend
Please sign in to leave a comment.