4 Replies Latest reply: Jan 20, 2015 10:12 AM by Beaulieu Mathieu

# Table expression, Set analysis filter on Month and Day?

Hi,

I have a new problem.

I want to write an expression to make a sum of values. But i only want to sum records where Months and Days are <= than current Month and current day.

For exemple :

Imagine that current date is : 20/01/2014

Day = 20

Month = 01

DateDayMonthDo sum?
01/01/201201

01

Yes
10/01/20131001Yes
20/01/20132001Yes
28/01/20132801No
15/01/20141501Yes
19/03/20141903No
20/11/20142011No
02/01/20150201Yes
15/01/20151501Yes
20/01/2015 (Today)2001Yes

The objectif is to compare sums of values from the current day with sums of values from others years but at the same date.

I tried this, but it does not work : =Sum({\$<Mouvement={"Livraison"}, Month={"<=\$(vCurrentMonth)"}, Day={"<=\$(vCurrentDay)"}>}Val)

Do you have an idea?

• ###### Re: Table expression, Set analysis filter on Month and Day?

Define a variable in the script :

LET myDate = Month(Today())*10 + Day(Today());

...

Month(myDate)*10 + Day(myDate) as MyMonthDayValue

...

resident myTable

Now you have a field to use in your expression

Sum({\$<Mouvement={"Livraison"}, MyMonthDayValue={'<=\$(myDate )'}>}Val)

let me know

• ###### Re: Table expression, Set analysis filter on Month and Day?

Ok, i understand, but i want to know why you do  *10 (Month(Today())*10) ?

• ###### Re: Table expression, Set analysis filter on Month and Day?

... it's a mistake, multiply by 100:

if Month is 3 (March) and day is 4 then you have 3 * 100 + 4 = 304

if Month is 3 (March) and day is 21 then you have 3 * 100 + 21 = 321

if Month is 11 (november) and day is 4 then you have 11 * 100 + 4 = 1104

if Month is 11 (november) and day is 21 then you have 11 * 100 + 21 = 1121

You have a natural sorting concatenating month and day

• ###### Re: Table expression, Set analysis filter on Month and Day?

Ok,

That's what I thought.