Set Analysis, Month, Today in Straight Table

[cpiocpio]

Hi

Firstly, I thought I had posted earlier, but I can not find it, if I did and If you do, find it then  my apologises

So in my straight table I need t return the SUM of a field, where the Dates in a field called : calDueDate is within the Current Month.

I used the Set Analysis Wizard to go my function statement, but I get nothing back

=Sum({1<Month(calDueDate)={$(=Month(Today()))}>}i_lifts)

Mike

[swuehl · Accepted solution]

Mike,

in your set modifier's field selection, there is only a pure field name allowed to the left of the equal sign (please compare to the syntax description in the Help file).

You can create an additional field in your data model for month or year month and then use this field in your set  modifier, like

=Sum({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

or if you need to select on your date:

=Sum({1< calDueDate ={">=$(=Monthstart(Today()))<=$(=Monthend(Today()))"}>} i_lifts)

[#3]

try like this

Create one variable like

Vtoday=max(month(calDueDate)) or month(today())

then implement the variable in set ananlysis

=Sum({<calDueDate={$(Vtoday)}>}i_lifts)

hope this will helps you

[#4]

Hi

Mike,

Use like this

Sum({$<calDueDate={">$(=Monthstart(today()))<$(=Monthend(today()))"}>}Sales).

see the set analysis wizard will helpful to you creating expressions in set analysis.

i hope it helps you.

Reagrds

Mahesh T

[cpiocpio]

Hi

I had found a similar post earlier, and it did not work, just tried yours and same results. returned 0.

I used

Sum({$<calDueDate={">$(=Monthstart(today()))<$(=Monthend(today()))"}>}i_lifts)

[cpiocpio]

Hi

Many thanks for this

I did as you suggested, added a new "field"/Variable to result in a returned value of Feb 2014.

Month(d_duedate) & ' ' & Year(d_duedate) as calDueMonthName,

Then using

({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

and it worked.

My prime mistake was having a function to the left of the = sign in a set analysis.

[cpiocpio]

Hi to ALL

Many thanks for your suggestions

I did as swuehl suggested, added a new "field"/Variable to result in a returned value of Feb 2014.

Month(d_duedate) & ' ' & Year(d_duedate) as calDueMonthName,

Then using

({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

and it worked.

My prime mistake was having a function to the left of the = sign in a set analysis.