6 Replies Latest reply: Feb 5, 2013 11:07 AM by Eleanor Odell

# set analysis- sum all if invoice date <= today not working properly

see snapshot of expression and results.

ultimately i want to exlude all rows for a table from the current selection that has an invoice due date > then today.

so i started with  a set analysis on the total balance to only include \$ if the invoice date <= today

sum({\$< date([AR Due Dt])= {"<=\$(=date(today()))"} >} [AR Balance])

i am getting 0.  I have tried putting in the format on the date 'MM/DD/YYYY' or 'YYYYMMDD'  no change

I tried using one users suggestion of changing the date to num()  no change.

I am getting a red mark below the {  in the string   {"<=\$(=date    have not figured out why

what am I missing?

and is this the correct approach to remove those rows from the table?

Thanks!

• ###### Re: set analysis- sum all if invoice date <= today not working properly

the mistake is the keyword DATE on the left of expression:

sum({\$< date([AR Due Dt])= {"<=\$(=date(today()))"} >} [AR Balance])

it is enough

sum({\$< [AR Due Dt]= {"<=\$(=date(today()))"} >} [AR Balance])

• ###### Re: set analysis- sum all if invoice date <= today not working properly

<groan>  I swear I thought that is where I started the whole exercise.  I must have had something else off and took a bad turn.  Thanks so much!

but now i have a bunch of 0 rows for when the date is greater than today...  which is what i expected-  what is the best way to remove those rows from the table?

• ###### Re: set analysis- sum all if invoice date <= today not working properly

What do you mean? You should not have rows with value > today.

• ###### Re: set analysis- sum all if invoice date <= today not working properly

correct - I do not want the table to show any rows where the due date is > today

• ###### Re: set analysis- sum all if invoice date <= today not working properly

There is a property in the chart:

IN sheet presentation (properties of the object) check "suppress zero values"

• ###### Re: set analysis- sum all if invoice date <= today not working properly

excellent-  right under my nose!  Thank you for your time...