Dynamic joins - how would you solve this ?

[Derek_T]

I have 2 tables (see pictures below): Date, BirthDay that I'm combining to create the third one BirthDayCount using joins on the "Date" field in the script.

I need the "CumulBday" column, the issue is, the table is generated at load time and is unaffected by the user selection. So when a user filter for "Bob" the BirthDayCount table does not change and the "CumulBday" column still contains all the values.

Even if I were to include a variable in the script, the variable would only affect the table at load time, but would not refresh every time the user change the selection.

Is there a way to handle the joins and build the table right into the "Sheet" section of the app ?

[edwin]

one way is to add a bridge between your birthdates and the calendar.  the idea is to create an association between the calendar and the birthdates when a name is selected only the dates you are interested in are selectable and it will be range and not just the exact equals.  for example, if your calendar starts from 2000 to 2022 and you select a name with birthdate = 1/1/2020 - all the dates from  1/1/2020  to 2022 are selectable and therefore reportable.  the simplest part is cumulative.  since all the dates are associated, you simply count the names associated to each calendar date - this means your expression will be super fast.  what makes it complicated is now you want to report prior years where there are no birthdates yet, you then need a backward looking association.  y will then have two types of dates one that is for cumulative calculation that starts from the first birthdate selected going up to whatever date you want and a prior date that starts from the first date in your calendar going to your last birthdate.  heres my code:

 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-CA';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';


NoConcatenate
Births:
load * inline [
Name, BirthDate
bob, 2022-01-20
cindy, 2021-03-22
megan, 2021-03-24
megan2, 2021-03-24
];

NoConcatenate
Dates:
load date(today()-iterno()+1) as Date
while date(today()-iterno()+1)>=date('2021-01-01');
load 1 AutoGenerate(1);

NoConcatenate
tmpBirthdays:
load distinct BirthDate resident Births;

outer join (tmpBirthdays)
load Date resident Dates;

NoConcatenate
Birthdays:
load BirthDate, Date, if(BirthDate=Date,1,0) as ExactDate, 'Cumulative' as type resident tmpBirthdays
where Date>=BirthDate;

concatenate(Birthdays)
load BirthDate, Date, if(BirthDate=Date,1,0) as ExactDate, 'Prior' as type resident tmpBirthdays
where Date<=BirthDate;

drop table tmpBirthdays;

[edwin]

this is how the DM looks like:

[edwin]

for the UI, i created a variable 

i didnt want to display the whole calendar - only up to the last birthdate.  this is for cumulative:

notice where it starts and ends - based on selected names.

this includes teh start of the calendar and individual birthdates my calendars starts 1/1/2021:

this is the first birthdate:

[edwin]

cumulative:
count({<type={'Cumulative'}, Date={"<=$(maxDate)"}>}Name)

the expression that counts exact birthdates:
=sum({<type={'Prior'}>}ExactDate)

you need to set these:

[edwin]

this solution leverages the associative engine which is what you really are buying.  you can implement a complicated expression for sure

[Derek_T]

Thanks Edwin unfortunately it did not work for me. The info was available in another table I was able to get it without doing all of this.