Tableau - Calculations

in this video we'll see how to work with calculations in tableau if you are used to Microsoft Excel and there are some inbuilt functions by Microsoft in Excel like some minimum maximum average you can calculate the difference so there are all these functions whatever you have in Excel similar to that you have in the tableau part so let us see some few calculations which are basic in W for the first time now you can download this workbook below this video the name of the workbook is calculations and the sheet name that is the database name is global superstore 27 in Excel file I have picked up the orders table here now what I'll do is I will try to create some calculations but for that I need to have some data here so first thing is I'll just double click on this subcategory double click on this sales part and after that I'll just double click on this profit here so now if you see I have the profit and sales with respect to the subcategory which is given here so what I'll do is I'll try to make into the descending order first I'll select this say as descending order sales I'll expand this option expand the data here yeah now what I wouldn't be doing is here is I want to calculate cost of these subcategory when can you calculate the cost when you have the sum if you subtract the profit from there you will be getting a cost so I have the sales if I subtract the profit from here I'll be getting a cost at this place but there is no cost to measure in this place if you observe carefully there is no cost so what I can do is I can manually create a cost field that's a cost measure which will subtract sales minus profit and it should give me a cost here creating a field manually it's very simple in this tableau part how do you create it you select any measure from here like this and you click on this drop down any measure you can select you can click on this option as create and the given app options given as create calculated field select this one you'll get a dialog box here you can give the name of the field here you can do the calculations part on the right hand side if you see you will be getting different types of calculations in tableau for example if I click on this drop-down I'll be getting number related calculations if I click on this and be getting some minimum Maxim tan cos theta all these options if I click on this drop-down date related functions like find out the day and find out the month year quarters and click on the drop-down there are different types of logical and so on so you can use any one of them if you want to hide and show you can just click on this button it will show and hide the this one this dialog box if you click on this show dialog box it will help you to write the you know calculation this is the extent ax which you can work on right now what we're doing is we want to find out the sales minus profit and I will be getting a cost here what is the name of the field you want to give I want to give the name of the field as cost and what exactly you want right here is I want to say I want to subtract sum of sales okay how do I write it you just type as some press tab when you write down some you'll get the options and functions which are given here you press tab from a keyboard what is the expression you want you just type as sales when you type sales it says there is already a measure if you see there's a green color measure sales which is already present as soon as you see that press tab from a keyboard like this and after that I will press subtract symbol sum of press tab again profit scroll down I mean you can just use your arrow keys to come down and let's say if I write s profit and press tab key from a keyboard remember whenever you find out the sales these are already inbuilt in the measures itself you can use those one and you have to press ok apply and okay here if you observe have got a cost to measure there's a small slight difference between this discount symbol and this one why there's an equal hash sign and here it's only hash sign equal hash sign says it is a calculated field it is not the automatic generated field or that's not a database field itself it is now newly created field so what you can do is you can just become this cost and you can drop into this place itself like this I'll just become the cost and I'll drop into this window here so if you find out if you see this sales - profit and cost let's try to calculate roughly if you subtract 73 if it's subtract 14,000 from 73 we'll get this answer as 53 58,000 361 that is nothing but the so like this you can keep on creating multiple calculated fields let's say some more few examples I want to calculate minimum and maximum of some particular field so first of all I'll pick up this as the product name I will pick up the product name double click on the product name and double click on the sales part so here it says there is there are multiple products if you see there are multiple products which are given here now in this products the total sales is 2 7:06 now to 7:06 is nothing but sum of the sales of all the products you see sum of sales of all the product this product might be sold 10 times 20 times if you want to find out what was the minimum and maximum but before that I'll show you how come to 7:06 came so for that you have to go to the analysis menu bar and you have to go to this analysis menu bar and you have to select this option as aggregate measures so now aggregate measures if you see it says I just expand so it is visible properly here I'll just expand this field anyone field you can expand yeah now when you expand it says this product 1.7 cubic foot it is 1 0 4 1 500 minimum was 2 0 8 and maximum was 1 0 4 1 it has been sold 1 2 3 4 5 6 7 times it has been sold and the maximum was 1 0 4 1 and the minimum was 2 0 8 so this was the entire detail about it same way this was the minimum was 6 and this was the maximum which was sold that the price amount was sold so what I can do is I have a option where I can find out minimum and maximum with some function that is by creating a calculated field so first of all I'll come back to the original part analysis menu bar and I'll click on this aggregate measures this is the 2 7:06 if you combine all of them it gives you 2 7:06 i'll create a calculated field where I can show minimum and maximum click on any field click on this drop-down click on this create calculated field and here I'll type as minimum and down I'll type as mi n sales that's it minimum of sales and I'll click on apply click on OK again if I want I can just pick up this minimum and I can drop it in this place like this and if you see it says the minimum is to 0-8 and here also the minimum is 6 same way I will try to create a maximum click on this drop-down click on this create calculated field type s maximum if you want you can give the name of the field anything I'll type as max of some max of some and click on yeah there's a mistake here if you see when you keep on typing you'll get an error message the calculated field contains error so what is the error it says sum is being called with brackets did you mean by float so I said this is the incorrect which I've written so what have to write as max brackett open I have to type as sales and then you have to press tab key from a keyboard and now see there's no error message which I've got click on apply click on OK and I'll be getting a maximum pickup and drop it remember the 14,000 which was the number now 1401 0 for 1 that was the number so minimum sales then was the Tuesday rate and the maximum amount of sales it was 1 0 for 1 so this we can keep on creating our calculated field now let's see how to work on the ceiling and flow so I'll select the ceiling and floor and now what I want is I'll just become this subcategory and I'll become this as sales so if you see here I've got the numbers here which are not in the ascending or descending order so I'll make into descending select and say as descending order which is given here but I want if you want ceiling and floor we should have some decimal numbers in this example so for that I need to format I'll just click on this drop-down click on this format click on this format pane be fault I just click on this place and I'll say as I want the number custom and I'll select s to decimal click outside so if you observe here I've got in numbers which are into decimal so I'll just click on this form and pane which I have got it here now what I'll do is I want to make into you know round off all these numbers so how do I round off this numbers using this ceiling and flow create a calculated field where if the number is eight to four point one four it should be as a 2 for itself if the number is six eight one point seven six it should become as six eight two so this is how I can make the ceiling and floor so let's see if I want to create a ceiling so I'll just click on this drop click on this create calculated field here I'll give the name s ceiling type here as in the pane I'll type it as ceiling sum of sales so I get that type here and I'll just click on this apply and ok so what I'll use I will pick up this ceilings and I'll drop into this part itself now if you observe this was in number six eight one point seven six it has been converted to six eight two that's the ceiling part wherever you find number is eight to four at dot dot one for it will be converted to the upper one same way if I want to be converted into the lower one that is nothing but giving us floor itself so for that I'll just click on this drop down click on this place click on create calculated field and here I'll type as floor and type as floor sum of sales that's it and I'll just click on this apply and ok so here there's a there's a measure which is created as floor so I'll become this floor and I will drop it in this place so if you observe I've got a floor all the numbers if you see it has taken to one level down itself so whatever decimal it has got it has rounded off to the lower one so if you observe here six eight 1.76 it was a number it has converted to six six eight one itself so it has taken to the ground part itself same ways ceiling will take you to top and the floor will take it into the bottom part itself so that is the method you can just keep on creating minimum maximum ceiling and floor and also we have seen how to create deep cost field cells so I hope you have understood the basics of creating a calculation field itself that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 73,480

Rating: 4.9238095 out of 5

Keywords: Tableau - Calculations, Tableau, Calculations

Id: rlmeQWDTI-s

Length: 10min 18sec (618 seconds)

Published: Tue Jan 23 2018