Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table of data of jobs which contains 3 date fields:
I wish to present a small table showing, dependant on a reporting calendar based selection which the user makes:
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
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.
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.
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!
I'm glad it worked.