Multiple if condition for an expression

[#1]

I have created a chart with expressions for aging such as 1-10 days, 11-30 days, etc.

I have a variable called AsOfDate.

The user should be able to enter the AsOfDate to see what the aging looked like as of the date they enter.

I basically have two if conditions that I will need in my expression:

I think it will go something like this:

for the current balance

if invoicedate <= asofdate and

if AsOfDate - num(duedate) <= 0 then sum(balance)

next one would be for the 1-10

if invoicedate <= asofdate and

if asofdate - num(duedate) >= 1 and asofdate - num(duedate) <= 10 then sum(balance)

Am I on the right track with this & how would my expression need to look to reflect this????

[Oleg_Troyansky]

Michelle,

no, not really... When you use "bare" field names like invoicedate, duedate, without enclosing them in any aggregation functions, QliKView will try to render the expression based on the available data set. Any fields not used with aggregation, will be used with the function only(), and assuming that you have many invoice dates and many due dates, the function only() will return a null.

Your expression needs to look like this, at the high level:

SUM( ... conditions ... balance) - all the conditions and the calculations need to happen within the function SUM()

Now, for the syntax of your conditions... If you dataset is really small and you want to keep things simple, you could get by with your IF conditions (I can't believe I actually recommend IF to someone!) Just keep in mind that IF inside SUM is very heavy and is not suitable for large data sets. A formula could look similar to yours:

SUM(

if (invoicedate <= asofdate and AsOfDate - num(duedate) <= 0 , balance)

)

For larger sets, you have to use Set Analysis which is a bit more complicated, especially when dealing with dates, because dates need to be properly formatted. However, I'd suggest the following syntax that selects Invoices that satisfy the same condition:

SUM(

{<  InvoiceNumber= {"=invoicedate <= asofdate and AsOfDate - num(duedate) <= 0"} >} balance

)

Your condition inside double quotes and curved brackets represents a search condition that determines what invoices need to be selected for the calculation. This way you might be able to avoid all the issues with date formatting...

best,

Oleg Troyansky