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

[ellyodelly]

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!

[alexandros17 · Accepted solution]

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])

[ellyodelly]

<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? 

[alexandros17]

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

[ellyodelly]

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

[alexandros17]

There is a property in the chart:

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

[ellyodelly]

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