Combine two views in one chart

Dashboard Designer SharePoint 2013/2016, Office 365 versions
1

Hi,
I have an accounts list for which i built two different views:

  1. Active Accounts (Modified > [Today]-14)
  2. Inactive Accounts (Modified <= [Today]-14)

I want to create one chart showing the number of active accounts vs the number of inactive accounts per account owner. How can i do that?

Appreciate your help.

2

Hello,

My proposition would be:

  1. Create a calculated column “Status” with a rule that would output “Active” or “Inactive”
  2. Use that column as the category field and, say, the ID field as the value field
  3. Check “Aggregate over category” and select “count” as the function

That should do it.

3

Can’t use Today() in calculated column, because it won’t update unless the item has been edited. So i need to deal with views here to get accurate results.

4

In that case you’ll have to do that in JS in a handler:

  1. Data Source -> SP List -> select a view with all items
  2. Data Source -> Advanced -> replace the requestSuccess function:

[code]handlers.requestSuccess = function(data, logger) {

var dfd = $.Deferred();
var _MS_PER_DAY = 1000 * 60 * 60 * 24;

var getSPCurrentTime = (function(webUrl){
	return $.ajax({
		url: webUrl + "/_api/web/RegionalSettings/TimeZone",
		method: "GET",
		headers: { "Accept": "application/json; odata=verbose" }
	}).then(function(data){
		return  data.d.Information.Bias / 60.0;
	});
})(_spPageContextInfo.webAbsoluteUrl);



getSPCurrentTime.done(function(value){
	var serverToday = new Date( new Date().getTime() - value * 3600 * 1000);

	$.each(data.items, function() {
		if (dateDiffInDays(this.Modified, serverToday) > 14)
			this.status = 'inactive';
		else
			this.status = 'active';
	});
	dfd.resolve(); 
});




return dfd.promise();

// a and b are javascript Date objects
function dateDiffInDays(a, b) {
	// Discard the time and time-zone information.
	var utc1 = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());
	var utc2 = Date.UTC(b.getFullYear(), b.getMonth(), b.getDate());

	return Math.floor((utc2 - utc1) / _MS_PER_DAY);
}

}[/code]

  1. Process
  2. Data Source -> Aggregation.

Group By: status
count = count of ID

  1. Process
  2. Dashboard -> Chart:
    Category: value
    Value: count

Probably best to test the code, especially with regards to different timezones (on client and on server).

5

Thanks for your response.
Unfortunately, the suggested solution didn’t work out, it freezes after request init, it never resolves the replaced code of request success. I’m using office 365 enterprise E3 plan here if that helps debugging the code. Please advise.

Thanks.

6

Did you check the “Modified” field in Data Source -> ShrarePoint List?

7

My Fault!
I added [Modified] to the view and now it works perfectly.

Thanks a lot for your excellent support.