Welcome to the Simplilearn Community

Want to join the rest of our members? Sign up right away!

Sign Up

Tableau Training | Rajesh Tukdeo | April 3-25

Hello Sir,

I am not able to find Sales-Target excel file in datasets which is required for Project-2. I have downloaded all the datasets .If you can please help where I can find the file.
 
Hello Friends,
I have a query related to Practice project under lesson. no.6. Though the solution has been provided but I am unable to find out how to get the values for " Profit ratio". Kindly help. Thanks
 

AKHILESH CD

Well-Known Member
Hello Friends,
I have a query related to Practice project under lesson. no.6. Though the solution has been provided but I am unable to find out how to get the values for " Profit ratio". Kindly help. Thanks
HI Learner, You should create a calculated field to get profit ratio. Refer this below link
 
In project 2, sales performance analysis, my data blending has a problem. I am attaching a screen shot of the box I get when I click edit blend relationships. It gives a huge list of fields other that category, ....etc.
As the blend is not correct, I am getting sales numbers not even close to the target sales numbers. I am attaching the bullet chart and the text figures as well. Can you please take a look ?
savita rao
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    211.1 KB · Views: 12
  • Capture2.JPG
    Capture2.JPG
    206.1 KB · Views: 12
  • Capture3.JPG
    Capture3.JPG
    205.5 KB · Views: 11

AKHILESH CD

Well-Known Member
In project 2, sales performance analysis, my data blending has a problem. I am attaching a screen shot of the box I get when I click edit blend relationships. It gives a huge list of fields other that category, ....etc.
As the blend is not correct, I am getting sales numbers not even close to the target sales numbers. I am attaching the bullet chart and the text figures as well. Can you please take a look ?
savita rao
Hi Learner , Please kindly check your email.
 
In project 2, sales performance analysis, my data blending has a problem. I am attaching a screen shot of the box I get when I click edit blend relationships. It gives a huge list of fields other that category, ....etc.
As the blend is not correct, I am getting sales numbers not even close to the target sales numbers. I am attaching the bullet chart and the text figures as well. Can you please take a look ?
savita rao
Umesh:
I asked the instructor about the Blending Relationship. I used the custom option. I used Category, Order Date and Segment. Please be aware Sample Superstore total number of records is 9994 and the order file the number of records is 1788. I do not get the chart exactly as the example of the project 2.
 

rajeshtukdeo

Active Member
Dear participants,

This is regarding the common problems you might be facing in Project 2 (Sales Target Bullet chart).. There are two datasets required for this project - primary dataset (Sample - Superstore.xls) and secondary dataset (Sales_Target.xlsx)

If you open your secondary dataset then you will notice that the dates in Order Date column are getting generated randomly using Excel formula =RANDBETWEEN(DATE(2014,1,1),DATE(2017,1,1))

1) First step is to change this formula to this one =RANDBETWEEN(DATE(2014,1,1),DATE(2017,12,31))
This will make sure that the Order date column will be populated using random dates between 1st Jan 2014 to 31st Dec 2017.. this is useful because primary dataset does have data for year 2017..

2) Once order dates are populated, remove the formula by copying the data and pasting only values.

3) Now open Tableau and connect to primary dataset first..Once connected successfully, click on Data menu, then 'New data source' to connect to secondary dataset

4) Once Tableau connects with both datasets, go to Sheet1, click on primary dataset and open 'Edit Blend Relationships' option

5) If you find any existing blending based on any fields, remove them and then do the blending only on three fields - Category, Segment & Order Date.. Hope it will resolve your blending issues..
 
Umesh:
I asked the instructor about the Blending Relationship. I used the custom option. I used Category, Order Date and Segment. Please be aware Sample Superstore total number of records is 9994 and the order file the number of records is 1788. I do not get the chart exactly as the example of the project 2.
thanks raul
 

rajeshtukdeo

Active Member
I am sending project 2 tableau link. Can you please take a look to see if it looks ok. I have placed 3 sheets on the dashboard. Let me know if this is correct. https://public.tableau.com/profile/...678005780/Salesandtargetdashboard?publish=yes
savita rao

Hi,

Thanks for sharing the project link.. Make sure that you have data blending that is similar to the shown below -


1619447265529.png


You have taken good efforts to prepare the individual charts as well as the dashboard.. Only thing that doesn't look correct is the bullet chart for year 2017 which is showing almost every record as below sales.. This is happening because probably you might not have added dates for year 2017.. Go thru my earlier post on how to fix this issue and rebuild the project ..

All the best !!
 

rajeshtukdeo

Active Member
Umesh:
I asked the instructor about the Blending Relationship. I used the custom option. I used Category, Order Date and Segment. Please be aware Sample Superstore total number of records is 9994 and the order file the number of records is 1788. I do not get the chart exactly as the example of the project 2.

Hi Raul,

I assume that you are discussing project 2 here.. A small correction.. The secondary dataset (Sales Target) has total 4603 records

Also you will not get exactly same bullet chart as shown in the project since the dates in this files have to be set randomly.. But if you can replicate the basic bullet chart with color coding (Above & Below Target) and the expected filters, that would be fine..

All the best !!
 
Hi,

Thanks for sharing the project link.. Make sure that you have data blending that is similar to the shown below -


View attachment 15420


You have taken good efforts to prepare the individual charts as well as the dashboard.. Only thing that doesn't look correct is the bullet chart for year 2017 which is showing almost every record as below sales.. This is happening because probably you might not have added dates for year 2017.. Go thru my earlier post on how to fix this issue and rebuild the project ..

All the best !!
Hi,
I have made the date change in the sales target dataset and fixed my tableau. In the excel sales target file, inspite of the change there are only a few records for 2017, just for the month of January. And that is what my tableau charts reflect. Can you please take a look now ? Do I need to do any cosmetic changes to the dashboard ?https://public.tableau.com/profile/...7678005780/linechartwithtrendline?publish=yes
savita rao
 

rajeshtukdeo

Active Member
Hi,
I have made the date change in the sales target dataset and fixed my tableau. In the excel sales target file, inspite of the change there are only a few records for 2017, just for the month of January. And that is what my tableau charts reflect. Can you please take a look now ? Do I need to do any cosmetic changes to the dashboard ?https://public.tableau.com/profile/...7678005780/linechartwithtrendline?publish=yes
savita rao

Hi,

The data still doesn't look good for year 2017.. I'm not saying that the chart is wrong.. chart is correct as per the data but as of now, it is showing records only for Jan 2017 ..

so follow these steps to make the chart better -

If you open your secondary dataset (Sales Target) then you will notice that the dates in Order Date column are getting generated randomly using Excel formula =RANDBETWEEN(DATE(2014,1,1),DATE(2017,1,1))

First step is to change this formula to this one =RANDBETWEEN(DATE(2014,1,1),DATE(2017,12,31))
(This will ensure that the Order date column will be populated using random dates between 1st Jan 2014 to 31st Dec 2017.. this is useful because primary dataset does have data for year 2017)

Once order dates are populated, remove the formula by copying the data and pasting only values. Then perform the blending of Superstore data and Sales target..

Now few suggestions regarding the charts you have designed -


1619499889672.png


1619500303532.png
 
Hi,

The data still doesn't look good for year 2017.. I'm not saying that the chart is wrong.. chart is correct as per the data but as of now, it is showing records only for Jan 2017 ..

so follow these steps to make the chart better -

If you open your secondary dataset (Sales Target) then you will notice that the dates in Order Date column are getting generated randomly using Excel formula =RANDBETWEEN(DATE(2014,1,1),DATE(2017,1,1))

First step is to change this formula to this one =RANDBETWEEN(DATE(2014,1,1),DATE(2017,12,31))
(This will ensure that the Order date column will be populated using random dates between 1st Jan 2014 to 31st Dec 2017.. this is useful because primary dataset does have data for year 2017)

Once order dates are populated, remove the formula by copying the data and pasting only values. Then perform the blending of Superstore data and Sales target..

Now few suggestions regarding the charts you have designed -


View attachment 15432


View attachment 15433




Hi,
I think I got the date info right this time. I have also incorporated all your other suggestions. Could you please take a look ?
https://public.tableau.com/profile/...roject_16187678005780/bulletchart?publish=yes
savita rao
 

rajeshtukdeo

Active Member
Hi,
I think I got the date info right this time. I have also incorporated all your other suggestions. Could you please take a look ?
https://public.tableau.com/profile/...roject_16187678005780/bulletchart?publish=yes
savita rao

Well done.. You are almost there.. a quick suggestion - instead of setting the bullet chart layout as "Entire View", change it to "Fit Width" since currently it looks cramped.. Setting it to "Fit Width" might produce vertical scroll bar but it will look better than current layout.. Give a try whenever you get time..
 
Well done.. You are almost there.. a quick suggestion - instead of setting the bullet chart layout as "Entire View", change it to "Fit Width" since currently it looks cramped.. Setting it to "Fit Width" might produce vertical scroll bar but it will look better than current layout.. Give a try whenever you get time..
Hi,
Will make the change. I am thinking I am good to submit now ?? Thanks so much for your help and super fast response time
savita rao
 
hi Rajesh,
My sales analysis project got approved, so started working on project 1- comparative study of countries. I have finished questions 1 and 2. I have created the 2 parameters. Stuck with the calculated fields for creating current year value. I get an error message for 'current year value' and 'convert data type' calculated fields saying " cannot mix aggregate and non aggregate comparisons or results in IF expressions " The year and year parameter fields both say Integer data type in the description box. Sending the screen shots of all my calculated fields. Could you please help ?

Savita Rao
 

Attachments

  • Capture 1.JPG
    Capture 1.JPG
    154.5 KB · Views: 13
  • Capture 2.JPG
    Capture 2.JPG
    149.9 KB · Views: 11
  • Capture 3.JPG
    Capture 3.JPG
    142.8 KB · Views: 9
  • Capture 4.JPG
    Capture 4.JPG
    106.4 KB · Views: 9
  • Capture 5.JPG
    Capture 5.JPG
    107.8 KB · Views: 8
  • Capture 6.JPG
    Capture 6.JPG
    76.5 KB · Views: 8

AKHILESH CD

Well-Known Member
hi Rajesh,
My sales analysis project got approved, so started working on project 1- comparative study of countries. I have finished questions 1 and 2. I have created the 2 parameters. Stuck with the calculated fields for creating current year value. I get an error message for 'current year value' and 'convert data type' calculated fields saying " cannot mix aggregate and non aggregate comparisons or results in IF expressions " The year and year parameter fields both say Integer data type in the description box. Sending the screen shots of all my calculated fields. Could you please help ?

Savita Rao
Hello Learner, Please Check your email.
 

rajeshtukdeo

Active Member
hi Rajesh,
My sales analysis project got approved, so started working on project 1- comparative study of countries. I have finished questions 1 and 2. I have created the 2 parameters. Stuck with the calculated fields for creating current year value. I get an error message for 'current year value' and 'convert data type' calculated fields saying " cannot mix aggregate and non aggregate comparisons or results in IF expressions " The year and year parameter fields both say Integer data type in the description box. Sending the screen shots of all my calculated fields. Could you please help ?

Savita Rao
Hi Savita,

The error that you are getting due to aggregate / non-aggregate values produced by "Year" field and parameter "Year Parameter".. Try ATTR() function to both fields instead of INT() function.. Hope that might resolve the issue..
 
Dear participants,

This is regarding the common problems you might be facing in Project 2 (Sales Target Bullet chart).. There are two datasets required for this project - primary dataset (Sample - Superstore.xls) and secondary dataset (Sales_Target.xlsx)

If you open your secondary dataset then you will notice that the dates in Order Date column are getting generated randomly using Excel formula =RANDBETWEEN(DATE(2014,1,1),DATE(2017,1,1))

1) First step is to change this formula to this one =RANDBETWEEN(DATE(2014,1,1),DATE(2017,12,31))
This will make sure that the Order date column will be populated using random dates between 1st Jan 2014 to 31st Dec 2017.. this is useful because primary dataset does have data for year 2017..

2) Once order dates are populated, remove the formula by copying the data and pasting only values.

3) Now open Tableau and connect to primary dataset first..Once connected successfully, click on Data menu, then 'New data source' to connect to secondary dataset

4) Once Tableau connects with both datasets, go to Sheet1, click on primary dataset and open 'Edit Blend Relationships' option

5) If you find any existing blending based on any fields, remove them and then do the blending only on three fields - Category, Segment & Order Date.. Hope it will resolve your blending issues..
I have the order date in the right order but when i copy and paste only the values in the excel sheet, i get different dates
 

rajeshtukdeo

Active Member
I have the order date in the right order but when i copy and paste only the values in the excel sheet, i get different dates
Hi Chisom,

You get different dates because RANDBETWEEN function is active till the formula values are replaced by actual values.. So there is nothing wrong.. Just make sure that once you paste the dates as values in Excel sheet, it shouldn't contain any formula..

regards
 
Hi,

The data still doesn't look good for year 2017.. I'm not saying that the chart is wrong.. chart is correct as per the data but as of now, it is showing records only for Jan 2017 ..

so follow these steps to make the chart better -

If you open your secondary dataset (Sales Target) then you will notice that the dates in Order Date column are getting generated randomly using Excel formula =RANDBETWEEN(DATE(2014,1,1),DATE(2017,1,1))

First step is to change this formula to this one =RANDBETWEEN(DATE(2014,1,1),DATE(2017,12,31))
(This will ensure that the Order date column will be populated using random dates between 1st Jan 2014 to 31st Dec 2017.. this is useful because primary dataset does have data for year 2017)

Once order dates are populated, remove the formula by copying the data and pasting only values. Then perform the blending of Superstore data and Sales target..

Now few suggestions regarding the charts you have designed -


View attachment 15432


View attachment 15433
Hello
Please how do i separate the legend to read above and below as separate on the legend? Do i need to include the line chart a well on the dashboard for project 2?
 

rajeshtukdeo

Active Member
Hello
Please how do i separate the legend to read above and below as separate on the legend? Do i need to include the line chart a well on the dashboard for project 2?
Hi,

Sorry not clear about the problem you have mentioned above... Can you pls put your screen-shots here for more clarity?
 
1633406787046.png
Hello
Thanks. I'm trying to split the legend using the colors to differentiate above and below the Target.
This is my dashboard for project2. <Does it look ok with the other plots as expected?
 

rajeshtukdeo

Active Member
1633576591003.png

Look at this screen shot.. You need to click the 'Center' button and enter your target value within the box below.. Then it will set Green color to all bars having values above the target and Red color to all bars with values below target..

Other way is to create a calculated field which tests whether SUM(Sales) >= Target value.. It will return either True or False for each bar.. then you can drag this calculated field on color button and select two colors of your choice..

Hope this helps..
 
Thanks for this:
I have done this but i cant seem to create 2 color tabs on the legend showing ''above and below''. When i use the calculated field, i can create a true or false dimension but i cant rename it from true and false to <Above and Below and i cant seem to display both as separate labels on the legend
 

rajeshtukdeo

Active Member
You need to modify your calculated field as -

IF SUM(sales) >= Target sales THEN
"Above Target"
ELSE "Below Target"
END


then drag this field on Color button on Marks card.. you will two colors (discrete) with options - Above Target & Below Target..

regards
 
Top