topic Generic calendar with Set Analysis question in App Development

Generic calendar with Set Analysis question

alexis — Sat, 16 Nov 2024 18:30:49 GMT

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.

count({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,TransactionDate=,

ItemID=P({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,TransactionDate=,

[DestinationDate]={"=$(=floor(SelectedDate))"}>})>}ItemID)

but get no results - ANY IDEAS WOULD BE WELCOME

Re: Generic calendar with Set Analysis question

tresB — Wed, 10 Jun 2020 06:29:17 GMT

[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.

Re: Generic calendar with Set Analysis question

alexis — Wed, 10 Jun 2020 07:39:15 GMT

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.

Re: Generic calendar with Set Analysis question

marcus_sommer — Wed, 10 Jun 2020 09:22:54 GMT

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:

count({$<[Transaction Year]=, [Transaction Month]=,[Transaction Day]=,
TransactionDate= p(SelectedDate)>} ItemID)

- Marcus

Re: Generic calendar with Set Analysis question

alexis — Wed, 10 Jun 2020 21:43:39 GMT

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