Conditional formatting based on aggregation totals

Hello, Yousif,
 
Thank you for writing to us.
 
We advise achieving the desired effect via Flexmonster API calls.
 
Here is a JSFiddle example: http://jsfiddle.net/flexmonster/om72q9ch/

In the example, when all data source files are loaded successfully and the grid is rendered, Flexmonster fires the reportcomplete event.
Inside of the reportcomplete handler (line 17), the getData API call is used to find min and max values.
Then the grid is colorized using these values.
 
You can also access the values of the totals using this approach.
 
Please let us know if this works for you and if you have further questions.
 
Best Regards,
Vera

Hello, Yousif,

Thank you for your reply.
 
We are glad the solution works for you. We will provide you with a special key via email shortly.
 
Regards,
Tanya

Hello Yousif,
Thank you for writing to us.
 
In case you need to define the slice with the smallest amount of cells, the best way to do it is to place the one field to rows and one field to measures area. Here is an example: https://jsfiddle.net/flexmonster/f3trys1b/. You can do the same thing with any aggregation. 
 
As for using percent aggregation the way you described, it is not possible to access the calculations without displaying them on the grid. Therefore, the only way here is either to switch to percent aggregation or display both of them.
 
Please let us know in case of any other question.
Regards,
Dmytro
 

Hello, Yousif,
 
 
Thank you for pointing this out to us and for providing a JSFiddle example.
 
 
1. We have noticed that the described issue occurs when a calculated measure is referring to another calculated measure.
 
For example, in the JSFiddle you have provided, the "price wavg" calculated measure is using the "sales" calculated measure:

measures: [
   {
     uniqueName: "sales",
     formula: 'sum("hours") * sum("price")',
     individual: true,
     caption: "sales",
     active: true
   }, 
   {
     uniqueName: "price wavg",
     formula: '"sales" / sum("hours")',
     individual: false,
     caption: "price wavg",
     active: true
   }
]

 
The issue is resolved when the calculated measure uses non-calculated fields.
 
For example, if instead of using the "sales" calculated measure in the formula for the "price wavg" calculated measure we use the defined formula for "sales" which consists of non-calculated fields, the issue is resolved:

measures: [
   {
     uniqueName: "sales",
     formula: 'sum("hours") * sum("price")',
     individual: true,
     caption: "sales",
     active: true
   }, 
   {
     uniqueName: "price wavg",
     formula: '(sum("hours") * sum("price")) / sum("hours")',
     individual: false,
     caption: "price wavg",
     active: true
   }
]

 
Here is a modified version of the JSFiddle for illustration.
 
 
However, we would like to point out that referring to calculated measures from a calculated measure should not result in an error.
 
Our team is working on a solution to this issue and the fix will be available in the minor release with the ETA 17th of June.
 
 
Please let us know if such a workaround would work for you.
 
 
2. We would like to confirm that getting all the values from the grand total column is possible.
 
Please note that the expandAll parameter needs to be set to true in the slice object that you pass to getData() in order for all 11 values to be returned:

var getDataslice = { 
   rows: [
           {uniqueName: "Income Band"},
           {uniqueName: "MaritalStatus"},
           {uniqueName: "[Measures]"}
   ],
   measures: [{ uniqueName: "Count", aggregation: "sum"}],

   expands: {
       expandAll: true
   } 
}; 

Here is a modified version of the JSFiddle for illustration.
 
 
 
Please let us know if everything works for you.
 
 
 
Best Regards,
Vera

Hello, Yousif,
 
 
Thank you for your reply.
 
 
Yes, you are right. Our team looked into this and we found the reason for the inconsistency.

The aggregation values are not correct because the calculated value "sales" requires the individual parameter to be set to true and in the "price wavg" calculated value which uses "sales" it is set to false:

measures: [
   {
     uniqueName: "sales",
     formula: 'sum("hours") * sum("price")',
     individual: true,
     caption: "sales",
     active: true
   }, 
   {
     uniqueName: "price wavg",
     formula: '(sum("hours") * sum("price")) / sum("hours")',
     individual: false,
     caption: "price wavg",
     active: true
   }
]

So, the workaround will only work in cases when both are set the same way. 
 
 
We are glad to hear that everything worked regarding getting the total values.
 
 
Please let us know if you have any further questions.
 
 
Best Regards,
Vera

Hello, Yousif,
 
The issue with getData() and calculated values is taking longer to fix than expected.

We would like to inform you that the fix is in progress and will be available in the minor release of Flexmonster with the ETA 1st of July.
 
Please let us know if this works for you.
 
Thank you for your patience.

Best Regards,

Vera

Hello, Yousif,
 
 
We are glad to inform you that the issue with getData() and cross-referencing calculated values was fixed.
Please refer to the following example: https://jsfiddle.net/flexmonster/kpyms1q2/
 
The fix is available in the 2.7.9 version of Flexmonster: https://www.flexmonster.com/release-notes/
 
You are welcome to update the component: https://www.flexmonster.com/doc/updating-to-the-latest-version/  
 
 
Please let us know if everything works fine for you.
 
 
Best Regards,
Vera