Additional Filtering

[marsbar]

Hi All

Another day - another question!!

I have a table - "Data" which I want to be able to filter by specific date ranges, I have the following date filter formulas on a slice which works brilliantly (Thanks to this forum!):

IN( [Payment Date Column], SELECT(Data [Payment Date Column], AND( [Payment Date Column]>=ANY(Date Filter[Start Date]), [Payment Date Column] <=ANY(Date Filter[End Date]))))

I also want this to be filtered by the Job Type as well (so only show e.g "Job Type A", "Job Type B" etc from the [Type] Column of the same Data Table.

Im then hoping to create various duplicated slices per different job type that then allow each to be represented by a chert view

Could someone please help!?!?

Thanks in advance

[scott192]

---

IN( [Payment Date Column], SELECT(Data [Payment Date Column], AND( [Payment Date Column]>=ANY(Date Filter[Start Date]), [Payment Date Column] <=ANY(Date Filter[End Date]))))

---

 You've pretty much done all the hard work in this expression.

All you need to do is add another check inside your AND() part like this:-

AND( [Payment Date Column]>=ANY(Date Filter[Start Date]), [Payment Date Column] <=ANY(Date Filter[End Date],[Job Type] = "Job Type A")

I think that should solve it and you can save each Job Type as a separate slice as you wish.

[marsbar]

thanks for this! I think the issue was the IN statement maybe preventing the extra filter of job type from working. Thanks for this!!

[Marc_Dillon · Accepted solution]

IN( [Payment Date Column], SELECT(Data [Payment Date Column], AND( [Payment Date Column]>=ANY(Date Filter[Start Date]), [Payment Date Column] <=ANY(Date Filter[End Date]))))

---

This is your expression in a Slice on the "Data" table? If so, you're doing unnecessary work, and causing unnecessary processing time. You can use a much simpler expression of just the inner part of your existing expression:

AND( [Payment Date Column]>=ANY(Date Filter[Start Date]), [Payment Date Column] <=ANY(Date Filter[End Date]) )

From there, I'd hope it's obvious that you can just add another condition into the AND() to check against the [Job Type]

[marsbar]

You are a genius! thanks so much for this... I really need to get my head around these expressions. I find the appsheet documentation a bit baffling when you get past the basics. 

Thanks so much for this mate. I may be bending your ear again soon (sorry!)

[Marisa]

Hi! I'm doing something similar but in a table (not in slice) in orden to filter by date in a form view.  that works. I've made an action with that: LINKTOFILTEREDVIEW("caja diaria", AND ([FECHA]>=[DE],[FECHA]<=[A]))

But I want to make more filters from caja diaria, for example by profesional (adding [profesional]=[_THISROW].[profesional] but it doesn't work.. 

could you help me?

[Marc_Dillon]

I don't understand what question you have, or what you're trying to do.

[Marisa]

Sorry; I have a table of daily income. From there, I have to pay a percentage to different professionals according to their income. as they are many I don't want to make a slice for each one. So I prepared a table for filters by date in order to export a csv for this view. I could filter by month but I want to add filter by professional and by category (medical consultation) and if possible add total sum and percentage to pay.

I know I can filter directly in the app, but I wanted to add total sum and percentage off this filter iff possible.

Thanks a lot!

[Marc_Dillon]

This still makes no sense. I suggest you create an entirely new thread. Show examples of your data, with screenshots, and describe what you are trying to accomplish. Also include some actual questions. And don't assume that you already know how you think you can accomplish it, focus on the bigger picture first.

[Marisa]

Sorry, I will try

this is my table. there are movements of money from medical consultations (patient payments), rents (professionals who pay the office), salary payments (secretaries) and other expenses. I am pediatrician and I have a team of pediatricians who I pay a percentage of the consultation. So, when the month finish, I need an excel from each one of them to see how many consultations they did, sum the total value (it depends on type of consultation, in Argentina we have private and prepaid systems) and calculate percentage to pay, which also may vary depending other factors... very complicated isn't it?

so I built this form

with this action inserted in the view

I tried to complete this formula in AND with [professional]=[_thisrow].[professional] and the same with category, but doesn't work.

I hope I have explained myself well, otherwise I will continue exporting csv and making the formulas there. Learning appsheet is being a nice and challenging hobby for me!! 😊