Set Analysis - returning data from Month before current selection

[#1]

Good afternoon,

I'm attempting to write a Set Analysis expression which takes the Month-Year currently selected, and returns a count of the data from the previous month which is divided by the total number of days in the previous month.

So far I have the following:

=count({1 < [Status Category] = {'FFP'}, Calendar_Date = {"<= $(=MonthEnd(AddMonths(Calendar_MonthYear_Select1,-1))) >=$(=AddMonths(Calendar_MonthYear_Select1,-1))"} >} [Calendar_Date])    //Set analysis expression

/Day(MonthEnd(AddMonths(Calendar_MonthYear_Select1,-1)))                      // This part of the expression divides the result from the set analysis by the total number of days in the previous month - this part is working

This doesn't return the correct data though.

I'm putting the 1 at the beginning of the expression to force the expression to use data from the entire dataset, and not just the selected dataset (which would only be the data from the currently selected month-year.

I then use $ at the part of the expression where I am utitlsing the currently selected month-year to use in the expression.

I hope I've described this well enough for someone to offer some assistance.

Calendar_MonthYear_Select1 is the name of the field which is being used to filter data, so this shows the 'Month-Year' in the format MMM-YYYY eg Mar-2016

Calendar_date is normal datetime - eg 01/03/2016

I'm trying to tell the set to only fetch data which is between the 1st and Last date in the previous month.

[swuehl]

I would suggest that you put your expression into a straight table expression and leave the expression label empty.

Then hover with the mouse over the expression header. You should see your expression with all dollar sign expansions evaluated and replaced.

What do you see?

Also note that your numeric search values should match exactely the values of the field you are searching in:

Dates in Set Analysis

And I assume you have a single value selected in Calendar_MonthYear_Select1 field, otherwise, use an aggregation function when using this field in your dollar sign expansion expressions, like

$(=AddMonths(Max(Calendar_MonthYear_Select1),-1))

[johnw]

Might just need to give it the right format for the date, like:

...Calendar_Date={"<=$(=date(monthend(addmonths(Calendar_MonthYear_Select1,-1)),'DD/MM/YY'))...

The monthend() function could be messing you up here, as it returns a timestamp, not a date.

[#4]

Thanks very much to both John Witherspoon and swuehl who assisted my in getting to my answer.

It turned out that I had a syntax error in my Set Analysis looking at the dates between <= and $

swuehl - your tip to put the expression into a straight table helped me to spot this, and the tips on making sure that the dates were in the correct formats across the board we're also essential, and the dates were mismatched.

For anyone looking for an answer on how to do the set analysis between 2 dates, this is the code I used:

=Count({1 < [Status Category] = {"FFP"}, Calendar_Date = {"<=$(=date(MonthEnd(AddMonths(Calendar_MonthYear_Select1,-1)),'DD/MM/YYYY')) >=$(=date(AddMonths(Calendar_MonthYear_Select1,-1),'DD/MM/YYYY'))"} >} [Aircraft Status ID])      //Set analysis expression

/Day(MonthEnd(AddMonths(Calendar_MonthYear_Select1,-1)))                         //This part of the expression divides the result from the set analysis by the total number of days in the previous month