Get Free Trial

Conditional distinct count

Sergii Valko 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
Thanks in advance

6 answers

Mykhailo Halaida Mykhailo Halaida Flexmonster 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:
Please let us know if this helps.
Best regards,

Sergii Valko 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:

  • for type = ‘t1’, ‘uniqueOwnPatientCount’ should be 2, instead of 0, and ‘uniquePortalPatientCount’ should be 1, instead of 0;
  • for type = ‘t2’ should be 1 and 1 accordingly;

Also please note on the grand total for ‘uniqueOwnPatientCount’. JSFiddle to demonstrate this Thanks in advance.

Mykhailo Halaida Mykhailo Halaida Flexmonster 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:
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:
Please let us know if you find our answer helpful.

Sergii Valko 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.

Sergii Valko April 15, 2020

Hello Mykhailo what about my question above, regarding hidden fields.

Mykhailo Halaida Mykhailo Halaida Flexmonster 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,

Please login or Register to Submit Answer