Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have one spreadsheet loaded into my app with two different date fields
1. Referral date
2. Referral commenced date
Is it possible to filter some visualisations in the app only by referral date and other visualisations only by referral commenced date at the same time?
Thanks!
You can use a common calendar for the two fields if you create a calendar link table like in my script below.
SET DateFormat='YYYY-MM-DD';
Transactions:
LOAD * Inline [
RefferalID, Amount, Referral date, Referral commenced date
1, 100, 2020-01-01, 2020-02-01
2, 200, 2020-01-01, 2020-03-01
3, 300, 2020-02-01, 2020-03-01
];
//Create calendar link
[CalendarLink]:
CROSSTABLE(Calendar, %date, 1)
LOAD DISTINCT
RefferalID,
[Referral date],
[Referral commenced date]
RESIDENT Transactions
;
//Create master calendar
for each _date in FieldValueList('%date')
Calendar:
LOAD
'$(_date)' as %date,
dayname('$(_date)') as Date,
Month('$(_date)') as Month,
YearName('$(_date)') as Year
AutoGenerate 1;
next
In order to pinpoint which calendar to consider in an expression use set analysis.
Referral commenced date
sum({<Calendar = {'Referral commenced date'}>}Amount)
Referral date
sum({<Calendar = {'Referral date'}>}Amount)
Thanks for the response!
What does the Transactions section mean?
Would i just replace this with the table my data is in?
Thank you