Trouble with dates and current selections in set expression

[timmarsh]

Hi all,

I read about 20 articles about previous periods before posting this, as this seems to be a really simple problem someone will know how to solve.

I can't post data, sorry, as I work in Health and our data is not de-identified and uses a data store.

So, I am trying to create a simple table showing the sum of something for this month, compared to last month.

My vMaxDate is 31/12/2017 at this stage.

Using a straight table, I can get all the data to display for Nov2017 using the below. (I realise there are more elegant ways of doing this with flags, something I'll get to eventually.) I realise I may have some redundant syntax - be gentle, I am still learning.

=sum({$<Date = {">=$(=monthstart(addmonths($(=vMaxDate),-1)))<=$(=monthend(addmonths($(=vMaxDate),-1)))"}, Day=>} DS_Complete_Counter)

I can also display the current month using:

=sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)

However, if I select a date, let's say Jun 3rd, 2017, I want the table to do 2 things.

Previous month to show May and current month June.

However, when I choose that date, both cols in the table are blank, so it looks like the current selection isn't working?

Date is not a num, I have DateNum for that, and not stored as a dual.

Happy to share Cal or anything else (I am using the standard MasterCal).

Sorry to ask such a simple question.

[sunny_talwar · Accepted solution]

Are you making selection in the same Date field as the one you have in your set analysis or is there another field that you make selection in?

=sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)

[timmarsh]

Ah. No. vMaxDate is a variable calculated before cal formed.

Stupid me.

So...

The current month (of selection, or max record date if no selection) is expressed as below and now works.

=sum({$<Date = {">=$(=monthstart((Max(Date))))<=((Max(Date)))"}, Day=>} DS_Complete_Counter)

However!

The previous month is still not working. I am missing something simple. I tested monthstart and monthend in textboxes with and without selections and it's working fine, so I am missing something simple!

Its expression is now:

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=>} DS_Complete_Counter)

What am I missing?

Thanks for your help, Sunny.

[Anonymous]

Hi ,

Could you please try this expression.

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}>} DS_Complete_Counter).

[sunny_talwar]

Try this

=Sum({$<Date = {">=$(=MonthStart(Max(Date), -1))<$(=MonthStart(Max(Date), 0))"}, Day=>} DS_Complete_Counter)

[Anonymous]

I dont know why it is taking such a long time to moderate this.

[timmarsh]

Hi Sunny,

Did you mean:

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=>} DS_Complete_Counter)

(I need to go back 1  month), also you still had the AddMonths -1 paramater in there but no add months.

to be clear, the current month is working perfectly in the state of no selections or with selections, and ignores day selection.

It's the previous month that is not selecting any records in the select state. In default state it works fine.

[timmarsh]

Unfortunately, still nothing in the select state.

[timmarsh · Accepted solution]

This is the correct previous month expression.

I've no idea why the forcing of values of month, year and day to empty makes it correct.  I'd love to understand why!

=sum({$<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))<=$(=monthend(addmonths(Max(Date),-1)))"}, Day=, Year=, Month=>} DS_Complete_Counter)