Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a GENERICcalendar whose date selections will bring me back results from tables that have no calendar attached. I also have a TransactionsCalendar that is linked to my Transactions table using the TransactionDate
In the "old fashioned way", I would have coded:
= count(if([DestinationDate] = SelectedDate, ItemID))
where:
SelectedDate is the date on the GENERIC calendar (selected either individually or range)
DestinationDate is a date on my Transactions table
The problem is that in the above statement my results are affected by selections in the TransactionCalendar that reduce my data set, so SET ANALYSIS is needed.
but get no results - ANY IDEAS WOULD BE WELCOME
Hi,
Your answer is very close to the answer that in the end proved easier than I first thought.
There are indeed 3 dates:
TransactionDate: This is the date that links the Transactions table with the TransactionCalendar.
DestinationDate: This is a date in the Transaction table
SelectedDate: This is the date that is in a Calendar "island" that is not connected with any table but there for the user to select from
The answer is very close to your suggestion but subtly different:
count({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,
TransactionDate=, [DestinationDate]= p(SelectedDate)>} ItemID)
Thanks you Marcus for your input
[DestinationDate]={"=$(=floor(SelectedDate))"}
the above could work if your destination date is integers, else you have to try to make the format matching while comparing, something like:
[DestinationDate]={"=$(=Date(SelectedDate, 'desiredDateformat'))"}
Even if this doesn't give you the right result, you might have to work on data modelling then.
Thanks for the reply - ALL dates are integers so it's not a date formatting issues - I was hoping that it would be a Set Analysis issue.
For example:
count({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,TransactionDate=,
ItemID=P({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,TransactionDate=,
[DestinationDate]={">=$(=floor(min(SelectedDate))) <=$(=floor(max(SelectedDate)))"}>})>}ItemID)
works fine but it assumes contiguous dates. Using the above formula does not account for "gaps" in the dates. For example if I selected 1/4/20, 2/4/20, 4/4/20 the above formula will also add 3/4/20 which was not selected.
In your expression are 3 different dates but you mention only two calendars and their linked dates - what is the third date - [DestinationDate] - for a date? If there are only two dates - one related with the facts and one not related (from a loosen table) which is just for selecting the following should work:
- Marcus
Hi,
Your answer is very close to the answer that in the end proved easier than I first thought.
There are indeed 3 dates:
TransactionDate: This is the date that links the Transactions table with the TransactionCalendar.
DestinationDate: This is a date in the Transaction table
SelectedDate: This is the date that is in a Calendar "island" that is not connected with any table but there for the user to select from
The answer is very close to your suggestion but subtly different:
count({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,
TransactionDate=, [DestinationDate]= p(SelectedDate)>} ItemID)
Thanks you Marcus for your input