date in a set analysis

[lee_williams]

Hi,

Can anyone give any clue or syntax around why this quite simple set analysis expression doesn't work.

I have a table which updates daily and shows the position

Date	S	M
2018-02-10	10	32
2018-02-11	11	33
2018-02-12	13	34
2018-02-13	14	32
2018-02-14	16	31
2018-02-15	17	36

so I'm just trying to create a formula for a text box which pulls out the correct amount for that day

I'm using a variable to define the current date

vCurrentDay = Date(today(),'YYYY-MM-DD')             as I noticed that having it in normal date format  DD/MM/YYYY it didn't  match with the Date field I formatted date to match date field  'YYYY-MM-DD'

the expression

=sum({< Date={"2018-02-08"}>}M)    works fine

=sum({< Date={"=date#(today(),'YYYY-MM-DD')"}>}M) - fails returns 198  (which is the sum of the entire column)

=sum({< Date={"=date(today(),'YYYY-MM-DD')"}>}M)   - fails - return 0

and so expression used

=sum({< Date={"=$(vCurrentDay)"}>}M)   - fails - return  198 (which is the sum of the entire column)

=sum({< Date={"$(vCurrentDay)"}>}M)   - fails returns 0

I've tried upteen variations of the expression - all to no avail.

any help please?

[sunny_talwar · Accepted solution]

Try this

=Sum({< Date={"$(=Date(Today(), 'YYYY-MM-DD'))"}>}M)

[lee_williams]

Many thanks