Help with Multiple Values

[Adam Taufique]

All,

I'm currently working on a project that lists out the reports that are created by the various users at a hospital and running analytics on it.

The data that has been extracted has over 2,000 reports with various authors, departments, etc.

Here's an example:



My first thought was to have repeating rows that list the same report names and keep every value unique within the report, however when I created the PivotTable based off of this, I would get wrong data in how the data is counted.



I've also tried to separate the different values into their own unique columns (i.e. Department 1, Department 2, etc), however this also runs into issues when creating a PivotTable on this data.

Is there away to better design this and to have a PivotTable parse through the commas that are separating the values? I've also been playing around with PowerPivot and have had no luck.

 

[bobhc]

Good day Adam and welcome to the forum if you have not already done so reading this link will help you in the use of the forum...http://chandoo.org/forum/forums/new-users-please-start-here.14/


As to your question why not just turn your data in to a table and then filter any groups you want


.

 

[NARAYANK991]

Hi Adam ,

I have not understood what kind of analytics you wish to run using the above data ; that is really the question which you want answered ; talking about pivot table and Powerpivot is not germane to the issue.

The data itself is not very clear ; when you specify 3 formats of delivery and 2 destinations , what does it mean ? Does each destination get all 3 formats ?

Each line of data is actually multiple aspects in one ; we have a report associated with which we have a report description , and one or more authors. I do not know whether department pertains to the authors or not ; if so , is it possible that two authors from different departments can collaborate on a report ? Then there is data pertaining to the recipients of the report viz. destinations in the form of people ; lastly , there are the formats of delivery. I do not know whether there is any relationship between either the reports / authors / departments / people with the formats , since formats will be few in number , and the same destination person can opt for different reports in different formats ; thus a CEO may want one report in PDF format , and another in an Excel workbook.

If you can say what exactly you wish to analyse this mass of data for , probably a solution can emerge.

Narayan

 

[Chris van der Berg]

Hi Adam,
Try this file.

Regards
Chris

 

[Adam Taufique]

Chris - thank you for your reply. I'll take a look at the file and see what I can do to analyze it.

Narayan - I was unclear on how the data is used in my initial post. The report name is created by one or multiple authors, each associated with a department in the organization. Also, the format of the report can be singular or multiple to an intended audience (also can be a single audience or multiple).

So with the example I posted (we can ignore report description as I don't care about analizing that field):

Report 1 has been created by Bob Smith and Jane Doe, who both happen to belong to the same department (department is specific to the author). The report goes to the Financial Audit office and the CEO in the forms of a PDF, Excel and Crystal Report.

I want to run analytics on things like the count of the format of delivery, count of how many reports are created by a certain author, count of reports by destination, etc.

My main question is on design, what is the best way to design a multiple responded document. Having the design like the image above does segregate everything out for a simple count procedure, however things can get lost in making sure everything is separate (because of the blanks).

If the design is like the second image (see image below), then the trick is running a pivot table that segregates the data based on a comma (for example, separating out Jane Doe and Bob Smith). On the PivotTable, I want them to occupy their own space.

It's solely being used to see how data flows through the organization. There are no numbered values in the reports.

Thanks in advance. Learning a lot on these forums!

 

[NARAYANK991]

Hi Adam ,

If we consider the fields in your data , they are :

Report Name
Report Description
Author
Department
Format of Delivery
Destination

Except for the first two fields , which are single-value fields , the others are all multi-valued fields.

Since you say Department is specific to Author , can you clarify how two authors who come from different departments will be represented ? This may be simple if only 2 authors and 2 departments are concerned , so that we can say Author1 comes from Department1 , and Author2 comes from Department2 , but what if there are 3 authors and 2 departments ?

Similarly , if there are multiple Destinations , and multiple Formats of Delivery , does it mean that all Formats of Delivery are applicable to all Destinations ? For example , if the Financial Audit department , and the CEO are the Destinations , and the Formats of Delivery are PDF , Excel and Crystal Reports , it is possible that the Financial Audit department wants the report in Excel workbook and Crystal Reports formats ,while the CEO might want it only in PDF format ; how will the data capture this situation , and if it doesn't how is the analysis valid ?

Narayan

 

[Eric M]

I think the best way to organize it would be to use more columns, ie: Author 1, author 2, author 3, and PDF delivery, Excel Delivery, Crystal Reports Delivery (probably just 0 if no and 1 if yes).

You should be able to separate it as you did in your first post (hopefully you can fill in the report names on those blank lines, could probably do a formula to grab the last report name in a helper column if there are a lot of them) then just create the new headers and do lookups based on the report name.

Do you think this type of approach would work?

Edit: Hopefully I made this clear, I am unable to work up a sample file at the moment. If I am not clear, let me know I'll get up a template of what I had in mind in the morning.