Write Multiple Date with airSlate SignNow

Save multiple date

[Music] everyone at Semak I hear from enterprise Dino I'm gonna do a really detailed tutorial here on how to manage multiple dates in your effect table okay and the reason I've decided to do this is because after monitoring the enterprise DNA support forum for quite quite a period of time I get a lot of questions about this a lot this is just one of them that I've landed on here quite a recent one at timing of this recording and I know this can be confusing I know it can be confusing especially when you're just starting out and you're wondering how can I write a formula their labels me to calculate the new it calculates something where I have multiple dates in a particular row in my fact table so it could be like ordered eight versus Shepard 8 vs. expiration date or a rival date or something like that all of this information might be in one row of your data set and you need to do some sort of calculation like how many orders do we have currently being shipped or how many orders are waiting to be shipped or something project based how how many people have we got working on a particular project or how many days is this project been going those those types of insights again and look it's not it's not that easy if you're just having an unhappy I that's why I want to do a detailed I want to run through a detailed example here so so you can use this as a reference going forward okay now let's have a look at the tables just to be absolutely clear what I'm talking about here okay now this is a just a demo data set that I have and think about this like this is like you know there's so many scenarios with us is the case we might have so like an order date right an expiration date a create a date a requested edit an updated date so so many dates on one particular table now there's a number of things you have to get right yeah and the biggest place that people get get caught up or get confused is how to set up your model okay because it's basically it's a two-step process here you need to get your model correct then you need to calculate right the correct tax formula and then understand how it takes for me is working because it's a slightly more complex pattern but the great thing is it is a patent so you can actually reuse this technique over and over again whenever you come across this scenario now the ultimate calculation I want to show here is I want to show on any particular date on any date how many orders we have which are currently say live right so they are between the order date and the expiration date so on any day how many orders are still live that have been ordered but have not expired basically okay and this is you know basically just counting up information that sits between two dates right multiple dates and you could you know this could be because could totally vary depending on on what what what you're trying to solve for but the technique will be the same okay when I first come to the model just show you what you need to do here okay so the key here the key here is this okay so you look at look at how I've set this model up I have used inactive relationships and this is what you need to do okay to get this to get this to work to ultimately get the correct calculation you need you do not want what you do you can't have one relationship which is active basically if you go down this route where you need to calculate can you calculate logic like this where you will need to count you know things in progress right orders in progress sales in progress etc you need to have these inactive relationships and you'll see why in a second I will describe exactly why in a second why you actually need these because you know this is just one calculation you might be doing right you might have other calculations that require some sort of relationship but I'm going to show you how you can turn a relationship from inactive to active within a formula okay the reason why you have to have it like this especially if you want to run these specific Aleks is because there's no other way to do it right really that and they may they may well be to be honest but this is totally the easiest way to do it if you just understand being modeling aspect of it and get the modeling aspect right think they're likely is some other far more advanced and complex where you have to write your formula if you do want to have an active relationship but I don't recommend it I recommend this way totally it is my far simpler if you just get your mind around the model okay now we're going to write a formula here that basically works through it works through logic from the date from the date table anything it takes it takes con a context from the data table but then works through some advanced logic inside of this table to then calculate the results for us okay so this is ultimately the put a visualization here because I want to show you what this is actually saying okay so in this in this particular time frame here this is how many orders are between the created date was it the order date in the expiration date on any day okay and so you see here that very sort of around April 2019 spiked and there is a whole lot of orders still that expired so we're getting up to 5,000 and then it drops away because as we know all the all the orders expire right so maybe you know your dad's gonna look totally different to this this is just has just got random expiration dates our way into the future but at least you can sort of see what we're trying to do here okay we're trying to work out how many of each of these rows are sitting a sitting live or setting active on any particular date okay now let's drill into this table here and have a look at the formula they were writing okay so what we need to do is that we need to work out what our total orders are live between see how many alive on particular day to start off with just total waters really simple right of just an account rows of that data table then we want to count out how many orders we have on in particular day but we want to do it in a much different context right we want to work out you know is it is the new order date less there this particular day and is the exploration greater than this particular day for each individual audit right and they can count up all of those and count up all of those individual orders so let's just walk through it here okay so what we it every single row right we currently this is quite interesting we currently have zero context coming from the date table because you'll see here that if I ever we see over here the date in this table is actually coming from the date of table right but we don't have any active relationships here so nothing in theory is being filtered in this context we're doing all of the filter and inside of the formula okay now I want you to recognize have a look at this part from when I first max date and actually mandate on every single row here is just returning the date that's all it's doing okay mix woman it doesn't actually matter in this case but you do want it this way around because that's how you're going to give the total there okay so let's say 23rd of our 21st of March 2019 right that bit on this particular row right here this mixtape is equal to the exact date so what we're doing is we are on every single row here we are at a rating through because that's what you do inside a filter we are iterating through every single order date so we're iterating through the thing that's about 5,700 rows right we're looking at every single order date there and and working out is the any order date as any of those other dates less then or equal to the next date so 23rd 21st of March on this day if it is then we're going to evaluate it to true right so what's going to happen once we do this is we're going to have just a table of orders which set have a like an order date unless they're in the state then we're going to come down to this part I'm going to say and do similar logic but we're going to go through the expiration dates we're going to work through every expiration date of the table that remains here and we're going to say does the expiration date it also equal to is also greater than or equal to that same very same date and then if both of those evaluates to true so all the truths here and all the truths here then that is going to then we're going to count up that particular order and that's how we get this 477 because every 477 orders evaluated to true where the order date was less than this date and well it was probably actually more sorry but then when you included this particular logic it probably reduced the table even more of this virtual table even more and that's how we got the four sevens in and that's how it changes it as we go along right and then it does that evaluation at every single row so this is a very intensive calculation which is going on here and then the reason why we have the total here because think about what's happening at the total and the total there is no date context right so at all so basically what's happening as we are working through every single order here and saying is it less then or greater to the very last dates that we have in our date table so that's going to basically evaluate to every single order being true and then we're going to say we're mean as we work through all of those orders which I've elevated to can I say is the expiration date greater than or equal to the Mun date and a date table and these are also all going to evaluate to true as well and so that's going to give us the 5 7 4 3 which is also what is our total orders actually as well so so that's why we want to use the min and Max so that our total actually make sense as well okay so what I'm gonna and I'm gonna revert back to the model can say okay why sit it up like this now it's cleaner that's that that's the biggest reason why you want to do this and the other reason why you want to want to have these inactive relationships is because you want to be able to turn them on if you do say need some calculation say by order date right okay so say for example we want to I'm going to show you how to do this as well this is a real deep dive this one so and so into these multiple dates and REO so we're going to go total orders by what a date again now this is where you use a function called use relationship so I'm gonna go calculate total orders and then I'm going to come down to another row here I'm gonna go use relationship now this is why you need those inactive relationships if you you could in theory get this calculation here without any relationship funnily enough but you do need in an active one if you do want to create some other type of information or other type of calculations here like this so like total orders by by order date and so I'm just going to link up I'm basically going to turn it on I'm gonna turn on their relationship okay and so then I'm gonna go order date here and what I've what I've just done there is I have been going and manually so you see here I'll I'll expand this a little bit total orders by order date and so you see here now I'm getting this this interesting I'm just kidding how many orders we had on any one day right and it's interesting here's you see here we have a we have a bump of seven orders right seven orders happened on that date and that's why we have a bump of seven orders between three five six and three sixths right but basically all I've done with this is I've turned on this relationship this Wow here I've turned it on virtually okay now if you didn't have this relationship at all you couldn't do that right and then so that's why these inactive ones are really really important and then you just turn them on virtually with the use relationship and as soon as you've turned them on here you can then go and read branch out into all the different patterns and cetera that you could need like you know cumulative totals moving averages anything from there once you set up this court these core calculations okay so hopefully this is slightly longer than normal but I really wanted to discover a lot of key things here because in the support from here we get asked this all the time and I just know it as it was very confusing and it's been had to solve it a few times and and I think that a really strong video on how to do this was definitely required so hopefully you can understand now these these inactive relationships why you need to use them the formula pattern that you need to use income reuse and multiple different ways and then also how you can turn on these inactive relationships and why you would want to depending on sort of what calculation you want to show in your in your report okay hopefully hope you got a lot out of this one if you did throw the video like really appreciate it don't forget to subscribe to enterprise DNA TV lots of great content coming to you and look out for more videos very soon okay talk to you soon Cheers