Aggregation based on multiple column values

[DarrenG]

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

[diptarag]

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