Pivot tables are a Knack reporting function that are very useful to perform calculations and groupings of underlying records. Great for getting insights into your data. Here I go through some examples and how to set them up in Knack.
Interested in my Knack database services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave
Get a free Knack trial account here: https://www.knack.com/r/you-tube
If you are a fan of building great apps with Knack, please click the Subscribe button to join my channel, and ring the bell to receive notices on all my new instructional videos. Thanks!
Full Transcript
Hey folks, Dave Parrish here at Mac Builders.Today's topic again please like this videoand subscribe to the YouTube channel if you get any value out of these.But today's topic, I'm going to go over one of Mac's reporting functions.These are very useful called pivot tables.You might be used to them from Excel or Google Sheets or something, but I'll explain in detail how Mac uses them.Real quick, here's an article.Mac doesn't go into too much explanation of them, but basically a pivot table does calculation based on records underneath it.And it sums, averages, whatever you want.In this case, this is a fake thing, but we have various donor people have made donations.So this pivot tail group, somebody who gave it and then it's grouping it by a particular fundraising event.So in this case it's summing all the different things and you can look at it in a variety of ways.I'll be back in a minute with more detail. Thank you.Okay, I'm back here to talk about pivot tables.I called up an app that I did,I talked about I did a case study on this attendance event, attendance tracking app.This is the organization that throws on these educational things and they're funded by state government and they have to do periodic reporting on all the demographics.We have stuff regarding age, race,Hispanic, all kinds of stuff.And they have a lot of events that go on.They have over 1000 attendance records.So we want to get insight into all these things.I'm going to jump over to live at here's a pivot table.There's a bunch of different things we track, butI'm going to just look at this one.This is looking at Hispanic origin or not.So it's Hispanic origin or not, they have to report that to the state.Now what this is doing is all those over a thousand records, it's adding them all up, but they have to report in different ways.And by filtering you can do all kinds of we can put in the range of dates.So if they have to do it by quarter, we can do it before this date and after that date to get specific things they could get all we just need to look at mail or whatever, the stuff they have to get.But all that's driven off of building this pivot table, looking at all those records andthen doing dynamic things to it.Another nice feature, if we want to look at who isbetween zero and four H, we can click on that and it's going to give us information we can drive down.That's an option, but we're going to look at this particular table and see how itwas constructed in the back end.Let's go over to pages Hoarding, and the one we're looking at is this one.So let's engage that.Now the first thing up here, we're looking at the tenants line item record.So every time someone was tenant that's what the source of this?So when you make it, you'd add something, you'd select the right table and in this case wewant to look at all of them.If you needed to filter by something, you can do that if something made sense.But if we look at the age here, my house is having problems back here, group by ages.These are the rows.So if we go back here to the backend, the first thing, what rows do we want?You can have all kinds of choices.In this case, hey, we want to look at age, thatis, select rows that are all the age options we have.And then the other thing we want to look at in this case, we usually don't use Pivot columns.What this does is we have a value in it.Are you Hispanic?Yes no before, not to say this three, it's multiple choice.If you pick columns, it will spread whatever the values area cross as the columns it could be by month two.That makes it easy.Instead of having to make, let's say twelvemonths, define it by January, all this different stuff, it just spreads them across.I'm going to get back to that at the end, by the way.And what do we want to do with these?We want to count them and there's different operating things you could do with the calculation, but in this case we just want them to count.So that's what it does.It spreads it across and it looks at all the stuff underneath it counting them and it creates a nice table.Then we can have a sum here at the bottom we're saying hey, make a column sum here, we want to sum that.Now back to this Pivot comp thing.I don't know if it's a bug if something doesn't work out.Should it be nice here to have a column at the end to the right that sums up all these, if you just look at it.But how Nat does this since we've just picked one value here?Hispanic origin?Yes no for not to say it considers that to be one value or one column, even though it's actually making it into three in this case.And you'll see down here where you have the option to get row summaries.Row summaries only work with two or more calculations.This I don't like, this you have to live with.I think there's some custom code you can apply to get that last column at somesense, but natively that's not going to work.And then there's other options here.Sometimes you don't want to count values that don't exist, meaning you have a record, but that value doesn't exist in it you can say exclude groups and then do that.And this ability here in the live app to click and then see the underlying things that are driving the value that's really useful.You do this click expand group records, and that's what it does.There are other filter things that I'm not going to get into, but it takes a little while getting used to these, but they're really helpful when you're reporting that stuff.So that's what I got for this one.Appreciate it, guys. Thanks.