Using Set Analysis to calculate opening & closing balances from a date field selection

[#1]

Hi All,

I have a table of data of jobs which contains 3 date fields:

• Start Date (the date a job began)
• End Date (the date on which a job will expire if it is not done)
• Completed Date (the date the job is completed, if it is completed)

I wish to present a small table showing, dependant on a reporting calendar based selection which the user makes:

• The opening balance of open jobs
• The number of new jobs raised
• The number of compeleted jobs
• The number of expired jobs
• Closing balance of open jobs

I have managed to get expressions like the following to work (this is the one for the closing balance of jobs):

SUM({1<[Offline Begin Date]= {"<=$(=MAX([ReportingDate]))"},[Offline End Date]= {">$(=MAX([ReportingDate]))"},[Completed Date]= {">$(=MAX([ReportingDate]))"}>} [Offline Room Count])

However, as I have used the '1' qualifier at the beggining of the set analysis, I am ignoring any current selections on any fields other than [ReportingDate].

As soon as I remove this '1'; i.e. in order for the user to be able to apply selections across other fields, such as regional information, then the expression fails and returns a zero value.

Can anyone suggest a solution to this problem?

I have pasted below my data structure, as I am sure this is the root of my issues..

Thanks in advance

[mike_garcia · Accepted solution]

Hi,

The only reason for it to return a zero value is because there is no [Offline Room Count] that fulfills all of the conditions (Selections + Set Expressions). When using Dates in Set Expressions, it is often necessary to explicitly exclude selections in, for example, month, year, period, etc that may cause the Dataset in play to be reduced and automatically exclude, before even calculated, the values you are looking for in your Set Expression.

I dont know the logic behind the relation of all those dates, but my guess is that you should explicitly exclude selections in all fields related to Reporting Date, and still keep selections in other fields, like Region and so. Example:

SUM({$<[Reporting Date] = , ReportingYear = , ReportingPeriodDesc = , [Offline Begin Date]= {"<=$(=MAX([ReportingDate]))"},[Offline End Date]= {">$(=MAX([ReportingDate]))"},[Completed Date]= {">$(=MAX([ReportingDate]))"}>} [Offline Room Count])

I hope this helps.

Mike.

[#3]

It's going to take me a few run through's of that to understand exactly why it is working, but it is.

Such a simple solution - Thanks!

[mike_garcia]

I'm glad it worked.