Combine two views in one chart

by MohamedGaafar » Tue Mar 15, 2016 1:50 am

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.
User avatar
MohamedGaafar
 
Posts: 22
Joined: Sat Jan 23, 2016 12:17 pm

by Rostislav » Tue Mar 15, 2016 10:31 am

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.
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by MohamedGaafar » Wed Mar 16, 2016 12:01 am

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.
User avatar
MohamedGaafar
 
Posts: 22
Joined: Sat Jan 23, 2016 12:17 pm

by Rostislav » Wed Mar 16, 2016 2:34 pm

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: Select all
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);
   }


}



3. Process
4. Data Source -> Aggregation.

Group By: status
count = count of ID

5. Process
6. Dashboard -> Chart:
Category: value
Value: count


Probably best to test the code, especially with regards to different timezones (on client and on server).
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by MohamedGaafar » Wed Mar 16, 2016 6:12 pm

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.
User avatar
MohamedGaafar
 
Posts: 22
Joined: Sat Jan 23, 2016 12:17 pm

by Rostislav » Thu Mar 17, 2016 10:45 am

Did you check the "Modified" field in Data Source -> ShrarePoint List?
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by MohamedGaafar » Thu Mar 17, 2016 8:17 pm

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

Thanks a lot for your excellent support.
User avatar
MohamedGaafar
 
Posts: 22
Joined: Sat Jan 23, 2016 12:17 pm


Return to Dashboard Designer for SharePoint 2013