Profile Picture

Charts for columns with semi-colons

Posted By Bob Smith 12 Years Ago
Author
Message
Bob Smith
Posted 12 Years Ago
View Quick Profile
Forum Guru

Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)

Group: Forum Members
Last Active: 11 Years Ago
Posts: 69, Visits: 1

I would like to create a chart based on two column values in a SharePoint list (Title column can be used as a filter):

Title: Entry 1

Produce: Apples; Oranges; Bananas

Quantity: 10; 8; 14

I would like a bar chart that has Apples, Oranges & Bananas on the X Axis and the quantity values on the Y axis. If there is a simple way to do this then one could easily use a "merged" data column as produced by MS InfoPath.



Nevron Support
Posted 12 Years Ago
View Quick Profile
Supreme Being

Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)

Group: Forum Members
Last Active: Last Year
Posts: 3,039, Visits: 3,746

Hi,

We are not sure that we understand the organization of your dataset.

In general if you have multiple categories in a single field, you need to split that to records, so your dataset must be in some plottable format.
So you cannot have Apples; Oranges; Bananas in a single record value for the Produce column, instead you need to have a single record for Apples that has Quantity 10,
a single record for Oranges that has Quantity 8 and so on.

Please let us know if we did not interpret the dataset correctly...



Best Regards,
Nevron Support Team



Bob Smith
Posted 12 Years Ago
View Quick Profile
Forum Guru

Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)

Group: Forum Members
Last Active: 11 Years Ago
Posts: 69, Visits: 1
Hey! Sorry for taking so long to get back - I must not have been subscribed.

I am trying to use Nevron Charts to display data that was created by a multi-value checkbox or repeating field in an MS InfoPath form. For example, a form might have on it a field that states "Check all that apply:". InfoPath can turn that field into a semi-colon delimited entry in a SharePoint site column. For example:

Ice Cream Flavors that you like (Check all that apply):
Vanilla
Chocolate
Strawberry
Rocky Road

The resultant answer might look like this: Vanilla; Chocolate; Strawberry

That would be the literal value of the entry in a site column in the form library.

The answer in this situation is *most likely* the "Split" text function, although I'm not sure how to apply it in context. I was hopeful that there might be an example somewhere or more specific documentation regarding the use of "Split".

Nevron Support
Posted 12 Years Ago
View Quick Profile
Supreme Being

Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)

Group: Forum Members
Last Active: Last Year
Posts: 3,039, Visits: 3,746

Hi Bob,

A very tough requirement, since the dataset is not well organized for pivot purposes. However you can try these:

1. Create one category with grouping expression: =true - e.g. make a single data cell for the entire table.
2. For each choice that may appear in the list (Vanilla, Strawberry etc.) create a Values data groping with this expression:

=SUM(TONUM(LIKE(SPLIT(Fields!Produce, ";"), "Vanilla")) * TONUM(SPLIT(Fields!Quantity, ";")))

Lets explain this expresison a little. The split fuction splits an array of strings to another array of strings by a given delimiter.

The Fields!Produce and Fields!Quantity in our case return arrays, containing all records from the data set. If we have this set:

Vanilla; Bananas - 10; 20
Oranges; Vanilla - 30; 10

These functions will return these arrays:
Vanilla, Bananas, Oranges, Vanilla
10, 20, 30, 10

The LIKE function is a chain function, so we transform the first string array into a boolean array the arrays become:
true, false, false, true,
10, 20, 30, 10

The multiplication operator is also chained - so we multiply the two arrays giving us the result array of:
10, 0, 0, 10

Finally we sum with SUM funciton. So for the bar Vanilla we get a value of 20.
The other Values Data Grouping (for Bananas, Oranges etc.) simply have the same value expression but with Vanilla replaced with Bananas, Oranges etc.

So you can create a bar for each type of product and they will appear in a single cluster. Unfortunately you can not create a separate category, but you can use the legend to show the series - which will do the job we think. 




Best Regards,
Nevron Support Team





Similar Topics


Reading This Topic