1 of 1 people found this helpful
Few quick question regarding your data source.
1. Could you please tell us is it one time activity or long term activity to create this report?
2. Your data source is excel only or any DB?
3. Is there any provision to change the format of the data source?
After seeing your data I would suggest you need the format the structure of your data source first. Transpose the Date fields so that all the date columns comes in a single field with its description. Like this
If this is one time activity then you can connect your original source in tableau then select all the date fields and Pivot like this
Once done then you'll get Pivoted field names and Values like this. I've renamed the Pivot fields.
Once done then just go to sheet and play with the data. Drag the fields in canvas.
Above solution works well if its once time activity. Else reconstruct the structure of your source at database level. If your source is excel only then transpose columns then connect to source tableau.
If you can't change the format and your data source is excel only then connect your source with legacy connection there you'll have option to write Custom SQL query that will help you to transpose the data.
Let us know if you've any question.
What should the order of the phases be? They are different in the data source that you gave us.
Thanks for you descriptive solution, but I had considered this solution before, and realized that this approach will not be a suitable solution for my problem.
To answer your queries,
Currently I am using excel to design this report, but for deployment it will be connected to a oracle database, and its creation will be a one time activity.
The report would regularly get refreshed as the new data comes in the database.
Our data is going to be very huge and though the phases will be fixed to 6 or 7 phases, the leads will be in thousands initially.
Hence, according to your solution, the rows would become 1000*(6/7) times.
This would greatly impact the report performance, moreover require a lot of extra memory.
This is a major threat after deployment as this report will be viewed by many people on the server regularly and it should not take more loading time than a minute.
Is it possible to use some other representation to display these KPI's effectively, without modifying the data source structure?
The phase fields in the excel are in the correct format.
If you look at a sales cycle generally, it begins by lead generation and ends with revenue recognition (when the lead places its first order).
I will suggest here our focus should be trying to imagine the right way to display the transition of each lead from one phase to the other.
Hence being able to identify that on an average which stage of the sales cycle is working slowly and be able to make it faster.
We can still modify the phase later as we want. Also, for now, the stage field is redundant.
Kindly suggest if you have any creative ideas!
2 of 2 people found this helpful
If you've read the last para where I've mentioned if you don't want to change the format then use Custom sql to transpose the data source. In custom sql you can use union to transpose/pivot your data. E.g
Select Lead, Phase1 AS Date, "Phase1" AS Phase from Table
Select Lead, Phase2, "Phase2" from Table
Select Lead, Phase3, "Phase3" from Table
I've read somewhere tableau works well in depth (rows) of the data source rather than width (columns). Performance degrade when you've more columns. It works well in millions not in billion records.
Alright Let me try that, and see how that affects the performance.
My issue here would still be representation.
The graph would not let us drag lead name to colors once the lead number increases more than 30 or so, as no palette would provide 30 or more unique colors.
Also the graph would look very crowded with hundreds of multi color lines of leads. If we need to sort by top 10 leads with the shortest phase duration for any phase its still a challenge.
Maybe you can try using the data source I have provided to come up with a better representation.