Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner
Partner

Generic calendar with Set Analysis question

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
 

1 Solution

Accepted Solutions
alexis
Partner
Partner
Author

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

View solution in original post

4 Replies
tresesco
MVP
MVP

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

 

alexis
Partner
Partner
Author

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.  

marcus_sommer
MVP & Luminary
MVP & Luminary

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

alexis
Partner
Partner
Author

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

View solution in original post