Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ellyodelly
Contributor

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!

1 Solution

Accepted Solutions

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

6 Replies

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

ellyodelly
Contributor

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.

ellyodelly
Contributor

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"

ellyodelly
Contributor

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

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