asked on April 8, 2020
Hello, a little question. Is it possible to implement formula for the next case? We need to calculate distinct count of objects (for example by name) but only which have other field equals some value(for example 'own' == 1). Please have a look to the example https://jsfiddle.net/joutsk19/1/
Thanks in advance
⋅ April 8, 2020
Hi Sergii,
Thank you for reaching out to us!
In case you need to add particular conditions to your calculated measures formulas, the if conditional operator might come in handy. The syntax for this operator is quite straightforward, it goes like this: if(condition, then, else). Therefore, in your situation, the formulas will look something like this:
{
"uniqueName": "uniqueOwnPatientCount",
"formula": "if('own' == 1, distinctcount(\"patient\"), )"
},
As you can see in the example above, the else part of the expression can be left blank if you'd like to leave a zero value in a cell where the condition is not satisfied.
We've prepared a quick sample based on your JSFiddle to demonstrate this functionality, feel free to check it out: https://jsfiddle.net/flexmonster/6os09b3c/
Please let us know if this helps.
Best regards,
Mykhailo
⋅ April 9, 2020
Hello Mykhailo, thanks for your response, it looks reasonable on the glance but unfortunately, this solution does not work well. Please note:
Also please note on the grand total for 'uniqueOwnPatientCount'. JSFiddle to demonstrate this https://jsfiddle.net/3m1Lvpsx/2/. Thanks in advance.
⋅ April 10, 2020
Hi Sergii,
Thank you for your prompt response.
Normally in such cases, we suggest using the individual property for a specific measure inside the Slice object – this would ensure that the "own" field in if statement is being checked separately for each record (and not calculated as the sum of all "own" records inside a group, as it is by default).
However, setting individual to true will also result in aggregations for all other values in a row being calculated exclusively inside that row as well. This leads, for example, to distinctcount not recognizing the two rows of t1 – p1 –1 as identical and calculating them as two different ones, as you can see in the uniqueOwnPatientCount drill-through view in the following example: https://jsfiddle.net/flexmonster/v6k9mhyu/.
As an alternative, we can suggest using the report filters to display own and portal patients in two different views. This allows you to avoid the conflict described above. Please see the following JSFIddle sample demonstrating this approach: https://jsfiddle.net/flexmonster/o2h3fn1y/
Please let us know if you find our answer helpful.
Regards,
Mykhailo
⋅ April 14, 2020
Hello Mykhailo, thanks for your suggestion, but unfortunately it is not applicable, because the report, in general, has a lot of columns and not all is dependent on some property. The main goal is to build a complex report which will visualize all needed info(a lot of columns) and then have ability to export it all to one PDF, not by parts. If such filter applied, all independent columns would have incorrect values, because all data set would be filtered(would be much better to apply a filter for only one measure/column). It's not good.
In my opinion, it will be super awesome if you implement something like a window functions in SQL, this will provide a powerful mechanism for data aggregation.
But for now, I have another related question, have I some way to use in the formula hidden value? In other words, I could on my side prepare some property that combining several other needed aspects and then do clear 'distinctcount' on it, w/o any conditions. But I definitely don't want to see this property anywhere, like drill through or field list or elsewhere, so it should be hidden and be used in the formula simultaneously. Perhaps we can define someone custom property for a data type which will be a pointer for hiding via some custom callback.
Thanks in advance.
⋅ April 15, 2020
Hello Mykhailo what about my question above, regarding hidden fields.
⋅ April 15, 2020
Hi Sergii,
Thank you for your suggestions on window function-like functionality.
Please note that our roadmap is full at the moment, so we cannot provide you with any realistic estimations on whether this feature is feasible to implement in the near future. Still, we have taken your suggestion into consideration and we'll make sure to inform you if anything changes.
Speaking of the "hidden" field type, it basically excludes the field from the data altogether, which makes it impossible to use the field in calculated values.
Please let us know if you have any other questions we can help you with.
Best regards,
Mykhailo