Conditional formatting based on aggregation totals

Yousif Ahmed asked on April 29, 2019

Hi there,
 
I’m trying to apply a heatmap effect to the pivot grid, styling the cells based on the value as a percentage of the col/row/grid total. There are simple examples on the website that show how you can customise cells based on hard-coded values, but in most cases we do not know the correct min/max values in advance, and so it is difficult to set styling rules for the cells. Is there a way to use aggregation functions eg “percentofcolumn” or “percentofrow” when setting conditional format rules? Or alternatively can you access the values of the totals that are calculated by the control?
 
Kind Regards,
Yousif

10 answers

Public
Vera Didenko Flexmonster April 30, 2019

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

Public
Yousif Ahmed May 1, 2019

Hi Vera,
 
Thanks for the example – yes this looks like exactly what we need. Unfortunately we are unable to use the getData API call as it is unavailable in the trial version we are using to evaluate – is there some kind of developer key I can use to access this API call?
 
Kind Regards,
 
Yousif

Public
Tanya Gryshko Flexmonster May 2, 2019

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

Public
Yousif Ahmed May 8, 2019

Hi flexmonster, I have been trying to use the getData api call to get the total values from the pivot in order to build a heatmap, but I am struggling to find an efficient way of doing this. In your JSFiddle example, you get the min and max values by getting all the data from the pivot and looping through the entire dataset. While this would be fine for small datasets, we are building very large pivots from potentially over 1 million rows of data and so this would not be a viable solution in terms of memory and performance. You mentioned above that you can use the getData api call to get totals from the pivot, but so far I have not been able to define a slice/filter that returns only the grand total values. Is there a way of doing this? If we could get these easily we could store just the totals and use them to work out the percentage value of each cell during customizeCell…
 
Alternatively, is there a way I can utilise flexmonster’s ‘gridpercent’ aggregation for this purpose? The values of this aggregation are exactly what we need to render an accurate heatmap, but I cannot figure out a way of using gridpercent values without displaying it as a measure. Is there a way we could format regular count cells based on the value of a hidden measure such as gridpercent?
 
Kind Regards,
 
Yousif

Public
Dmytro Zvazhii Flexmonster May 9, 2019

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
 

Public
Yousif Ahmed May 10, 2019

Hi Dmytro,
 
Thanks very much for the example, it was really useful and I can see now how we can use getData to get just the total values one field at a time, which will work for us.
 
One thing I have found is that if I try to define a slice with a calculated measure such as weighted average, getData() throws an error – ‘Cannot read property ‘0’ of undefined’. I have created a jsfiddle example based on the weighted average example on your website that demonstrates the issue – if you run the jsfiddle you will see the error in the console:
 
https://jsfiddle.net/yahmed/Lxn7sqdg/1/
 
Is there a way of getting calculated measures such as weighted average from getData()? Is this a bug or am I defining my slice wrong? It seems odd that the same slice will produce a valid report but will throw an error if passed to getData()…
 
Kind Regards,
 
Yousif

Public
Yousif Ahmed May 13, 2019

Hi Dmytro,
 
sorry to bombard you with more questions – I promise we will be purchasing full licences in the coming days!
 
I have another query about the getData api call and the example you suggested with one field and one measure. How would this work if you have multiple fields in your rows or columns? It seems like even if I define a slice with two fields in the rows array, only the total values for the first defined field are returned. I have made another jsfiddle which demomstrates this:
 
https://jsfiddle.net/yahmed/vjbym9g6/1/
 
At the moment it seems that if my report contains multiple fields, I can only get subtotal values for a particular field, rather than all of the grand total values for all the rows in the report. In the jsfiddle you can see that getData() returns only 3 total values for ‘Income Band’ even when I have both ‘Income Band’ and ‘MaritalStatus’ defined as rows in the slice. What I am trying to get is all 11 of the grand total values in the last column of the displayed pivot – is this possible? 
 
Kind Regards,
 
Yousif

Public
Vera Didenko Flexmonster May 13, 2019

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

Public
Yousif Ahmed May 14, 2019

Hi Vera,
 

  1. Thank you for the detailed response – I’m glad to hear the issue with calculated measures will be fixed soon as this is an important feature for us. Unfortunately the proposed workaround does not seem to give the right aggregation values when compared with the original example – notice how in your example using a single calculated measure the grand total value for ‘price wavg’ is 300 rather than the expected value of 24.96. I have played with some different measure definitions but I cannot get to the correct total value without using two calculated measures. This seems odd as the values for each cell are correct, which suggests the formula itself is correct – it is as though the grand total value is aggregated using ‘sum’ regardless of the specified formula. Since it is these total values we are trying to get from getData(), this workaround won’t quite work. Can you see a way to get the desired aggregation with a single calculated measure? If not, for now we will have to limit the heatmap feature to standard (non-calculated) measures and wait for the fix to be released.

 
2. With regards to getting the totals values using the ‘expandAll’ parameter – this is working perfectly, and I am able to get exactly the total values I need, so thank you for pointing this out.
 
Kind Regards,
 
Yousif
 
 
 

Public
Vera Didenko Flexmonster May 14, 2019

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

Please login or Register to Submit Answer