Calendar view using interactive dashboard filter

Dan_Longley
Participant II

Mimicking the demo app " Slice based on user input", I have an interactive dashboard filter that should allow me to select a Client Name using table Filter-Client. That bit works ok. Then it should show on the calendar all dates that the client has (previously) attended.

With this formula

CONTAINS(Register[Attendees],ANY(Filter_Client[Client Name]))

in a slice (looking at Register table) for the calendar view, Iโ€™ve managed to get the calendar to recognise that, for example, Anton has attended before, but it shows ALL the dates, even those that he didnโ€™t attend. In other words, itโ€™s a yes/no response.

How do I get the formula to check which dates Anton was an attendee and then return just those dates on the calendar view.

2X_4_4156e50f62796951cac07115aaebdd7748ced057.jpegT

Any pointers much appreciated.

Solved Solved
0 9 1,640
1 ACCEPTED SOLUTION

I wasnโ€™t completely sure how you were using the original expression.

For the slice filter criteria, you simply need to wrap the expression I provided with another IN() function. I am assuming that you have access to the RegisterID value is some way such as directly in a column in the โ€œClient Attendance Calendarโ€ slice row or through a Ref column. If you donโ€™t, be sure to add it.

IN([RegisterID],
   SELECT(Register[RegisterID], 
          IN(ANY(Filter_Client[Client Name]
             ), 
             [Attendees]
          )
   )
)

Note that I used RegisterID in two places. AppSheet should be able to figure out from context that the first is in the Slice dataset and the second is from the Register table.

View solution in original post

9 REPLIES 9

I believe you want to use the IN() function instead. CONTAINS()is a function meant to operate on text strings and not lists.

I think the function you are looking for is something SIMILAR to:

IN([Client Name], Register[Attendees])

This is assuming that:

  • [Client Name] is a column in the row you are operating on
  • the [Attendees] column is the name of the attendee - not the ID as in a Ref column.

If the data structure is different than I am assuming then post back and one of us can help get the correct IN() statement written with you.


@John_Baer Thatโ€™s really useful, thanks.

However, Iโ€™m not there yet! Iโ€™ve interpreted what youโ€™ve said to come up with:

IN(ANY(Filter_Client[Client Name]),Register[Attendees])

where ANY(โ€ฆ) reads the Full Name of the client Iโ€™ve selected (from the FilterClient table) - the needle

The haystack: does the Attendees column on Register table contain my selected client on any of the rows? Unfortunately, if the client exists in any of the lists (under Attendees), then the calendar view shows ALL the dates, not just, say, Sat 11 Apr for Anton. If I choose a client who doesnโ€™t appear on the register, then it rightly shows no dates, so I know the filter is connected correctly to the calendar view.

I thought about embedding the above in FILTER(), but I get: โ€œThe expression is valid but its result type โ€˜Listโ€™ is not one of the expected types: Yes/Noโ€

What am I missing here? Help!

Ok so the Register[Attendees] portion of the IN() function gives you list of the Attendees values from EVERY row in the Register table. I assume you have Attendees defined as an EnumList? So you will get a List of Lists which effectively one big list of ALL attendees in the Register table.

In your case, If I understand correctly, you are trying to get a list of registrations the Filter client is listed as an Attendee on. So you will need an expression more like this:

SELECT(Register[RegisterID], 
       IN(ANY(Filter_Client[Client Name]
          ), 
          [Attendees]
       )
)

This gives you a list of registrations that include the Filter Client Name.

I hope this helps!

Dan_Longley
Participant II

@John_Baer Thank you, I can see how that should work, but Iโ€™m still getting:
โ€œThe expression is valid but its result type โ€˜Listโ€™ is not one of the expected types: Yes/Noโ€

โ€œThe filter condition โ€˜=SELECT(Register[RegisterID], IN(ANY(Filter_Client[Client Name] ), [Attendees] ) )โ€™ of table slice โ€˜Client attendance calendarโ€™ must return true or false.โ€

Youโ€™re right that I use a UX form that allows the user to select from a long EnumList of Client Names (drawn from another spreadsheet table of client details). This results in a row in the Register table which includes a list of all attendees (Client Names) for that dayโ€™s class; the Count in the next column adds up the number of attendees.

Whatโ€™s the mis-match here, any thoughts?

I wasnโ€™t completely sure how you were using the original expression.

For the slice filter criteria, you simply need to wrap the expression I provided with another IN() function. I am assuming that you have access to the RegisterID value is some way such as directly in a column in the โ€œClient Attendance Calendarโ€ slice row or through a Ref column. If you donโ€™t, be sure to add it.

IN([RegisterID],
   SELECT(Register[RegisterID], 
          IN(ANY(Filter_Client[Client Name]
             ), 
             [Attendees]
          )
   )
)

Note that I used RegisterID in two places. AppSheet should be able to figure out from context that the first is in the Slice dataset and the second is from the Register table.

Dan_Longley
Participant II

@John_Baer Yes, it works, thank you so much!! Iโ€™m not sure about whether I have a ref column, etc but it appears to be functioning correctly.

I would never have worked out to nest an IN() within another, so will now study it until it makes sense to me

Thanks again for your prompt support

Hi,
If you donโ€™t mind, can you please share the link of your APP because I am also trying to create the slice-based upon user input in my calendar but somehow itโ€™s not working for me.
Thanks

Hi
Iโ€™m really sorry, but itโ€™s been 2 years, and it would take me a couple of days to work out how the code works. I did do a copy at some stage, but it broke, so was probably no use. Perhaps address your question to @John_Baer ?

As much as I hate to not to have a good article from here, this is the best explanation Iโ€™ve found for Dynamic Dashboard:

Also, the app refered on the OP is this:
Slice based on user input

Top Labels in this Space