DarrenG

Aggregation based on multiple column values

Recommended Posts

Hi,

 

Here is a brief schema from a SharePoint list I am trying to create a chart based on:

Name (text)

Date (Date/Time)

Status (choice)

Cost (Currency)

 

I am trying to create a 2D 2-Y combination graph. This is causing me 4 problems I hope you can assist with:

 

1) On the X axis, I need to display every month in a specific range (i.e July'13 to Jun'15), even if there aren't any values in the the list for that month.

2) On the primary Y axis, I need to have the *cumulative* count of each record that has the date in that month or prior (i.e., 3 records have a date in July'13, 2 records have a date in Aug'13, so July'13 will show 3, and Aug'13 will show 5) as a line that will keep increasing.

3) On the secondary Y axes, I need to have the *cumulative* sum of each record's Cost column that has a date for that month or prior (similar to 2)) as a line.

 

At the moment I can't seem to accomplish none of those 3.

 

I would also like to have a cumulative shaded area based on the values in another list (month (Date/Time) and estimate (number)), but not sure if this is entirely possible to use more than one list in Collabion?

 

Thank you,
Darren Goodair

Share this post


Link to post
Share on other sites

Hi Darren,

 

Please find below my comments -

 

1) On the X axis, I need to display every month in a specific range (i.e July'13 to Jun'15), even if there aren't any values in the the list for that month.

 - You can filter the date with 'In Between' filter and give a date range like 1st July, 2013 to 30th June, 2015. That will meet your first requirement. And if you want to show a blank value for the months which do not have any data - check "Include items whose data values are empty in the filtered column(s)" inside 'Filter Data' steps.

 

2) On the primary Y axis, I need to have the *cumulative* count of each record that has the date in that month or prior (i.e., 3 records have a date in July'13, 2 records have a date in Aug'13, so July'13 will show 3, and Aug'13 will show 5) as a line that will keep increasing.

- You can achieve a similar thing if you use Single Series Waterfall Chart, but we do not have any such chart for multiple series/dual y

 

3) On the secondary Y axes, I need to have the *cumulative* sum of each record's Cost column that has a date for that month or prior (similar to 2)) as a line.

- I am afraid this is not possible

 

I would also like to have a cumulative shaded area based on the values in another list (month (Date/Time) and estimate (number)), but not sure if this is entirely possible to use more than one list in Collabion?

- As of now we do not support fetching data from more than one list for a single chart. But we have plans to add support in future

 

Kindly let me know if you need any clarifications or assistance

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now