Solved

# Conditional formula

• 7 replies
• 475 views

Userlevel 3
+1
• Semi-Pro II
• 92 replies

Hello, can someone please kindly help me with this?

I want to display the value of ‘not null’ VAT Sales (or) ‘not null’ VAT Exempt (or) ‘not null’ ZERO Rated in “Total Sales”. I need a formula, I’ve been trying to use ISNULL and IFF but I’m wrong. Can anyone please give one. Thanks so much!

icon

Best answer by DConcannon 21 December 2022, 20:49

View original

### 7 replies

Userlevel 6
+2

@paula012 Have you tried

=IFF([ARInvoice.CuryVatTaxableTotal] = 0.00, [ARInvoice.CuryVatExemptTotal],[ARInvoice.CuryVatTaxableTotal])

Userlevel 3
+1

Oh yes @DConcannon , this exactly is what I have now. It works, but I want to add another condition here for the next field which is if Vat Exempt  = 0, then the Total Sales would give me the value of Vat Sales which is if also 0, then I’ll get another value which is the total sales itself. Am I making sense to you? Thanks so much again!

Userlevel 6
+2

@paula012 I am not sure I completely understand, but if you are wanting to display total sales before  taxes and document discounts, can you just use [ARInvoice.CuryLineTotal] and not use IFFs?

Userlevel 3
+1

Thanks. Ok let me try again, sorry if I can’t explicitly word my intentions here. I can display the Detail Total ONLY IF all of them are 0.00. That’s what I’m missing in the condition because what you first gave already works, which is if VAT Sales is 0.00, then VAT Exempt value displays, vice versa.

Or could I say I’m needing a “nested” IIF statement? Please help me w/ this. Thank you!

Userlevel 3
+1

Hi @DConcannon, this worked! Can you see through here what I wanted as an output? If VatTaxable is 0, I want the VatExempt. But if VatExempt is also 0, then I want the CuryLineTotal.

And since, VatTax has value, I got its value. But I don’t have any sample that I could test for the condition that if VatExempt has value, it would get the CuryLineTotal. Would it? Thank you!

Userlevel 6
+2

@paula012  IIF stops processing once it finds a True condition, so if ...TaxableTotal is 0.00 you will always get ...ExemptTotal even if it is 0.00.  Try the formula below, it is very similar, the nested IIF just starts sooner.

=IIF([ARInvoice.CuryVatTaxableTotal] = 0.00, (IIF([ARInvoice.CuryVatExemptTotal] = 0.00,[ARInvoice.CuryLineTotal] , [ARInvoice.CuryVatExemptTotal])),[ARInvoice.CuryVatTaxableTotal])

The image below images contain a single test I did with the New formula and your Existing formula.

Do you have a test tenant or a local installation where you can build the all your Invoice cases to verify you are publishing a good report?

Userlevel 3
+1

Do you have a test tenant or a local installation where you can build the all your Invoice cases to verify you are publishing a good report?

Sorry for the late update. This works, yes. But I couldn’t make another sample for the other two cases yet. Thank you very much!