Urgent Assistance Needed for Calculated Field

Hello,

I need help creating a calculated field that counts distinct names where all associated values are null.

For example:

  • If “Vaibhav” has all null values, the calculated field should show a distinct count of 1.
  • For “Arun,” it should also display a distinct count of 1.
  • However, for “Ram” and “Shyam,” the distinct count should return 0.

I’ve attached a snapshot for your reference.

Please guide me on how to achieve the desired output.
For any another query feel free to contact me.

Best regards,
Vaibhav.

Hi @Vaibhav.narwade ,

In a table that shows the names and the new field the calculation works:

ifelse(count({Hrs})=0, 1,0)

If you need a table with all three existing fields the question is more tricky.

Best regards,
Nico

Hi @Nico,

Name is as String Datatype
Hrs is as Int. Datatype
For Hrs There are NULL Values

Condition :
suppose we filter any name and for that name observed all Hrs values are null/0
then it will show the distinct count of name.

Example :
Arun | Null
Arun | Null
Arun | Null
Vaibhav | Null
Vaibhav | Null
Vaibhav | Null
Shyam | Null
Shyam | 1 Hrs
Shyam | 2 Hrs

Output :
Head Count
Arun = 1
Vaibhav = 1
i.e. Head Count = 2

we don’t consider Shyam in this case.
I hope now you understand my question. :slight_smile:

Best,
Vaibhav.

Hello,

Please find the snapshot for your referance.

Best,
Vaibhav Narwade.

Hi @Vaibhav.narwade ,

thank you for your additional information.

The Head Count For 0 Hrs is not possible with a calculated field.
For that you would need to count the distinct names if the sum over the hrs per name equals 0.

As soon as you want to count_distinct a sumOver function QuickSight does not allow the calculation.

I think you have two options:

  1. accept, that your output is the pivot table in the middle of your screenshot (that calculation is possible)
  2. precalculate the sum of Hrs per Name before you load the data into quicksight.

Best regards,
Nico

Hello @Nico,

Thank you for your clarification.
I apologize for the delay.

Regarding the count blank in QuickSight, it seems this should be addressed as a provision.
For now, it’s acceptable to perform the calculations before loading data into QuickSight.

However, I have concerns about the direct query; it may not function properly in that case.
You can consider this as something that needs further development.

Best,
Vaibhav Narwade.

Hello @Vaibhav.narwade ,

thank you for your reply.

A Quicksight Administrator can add feature request.

Not sure what would be the best topic. Maybe “Using distinct_countIF with partition(LAC)”.

Best regards,
Nico

Hello @Nico,

Thank you for your reply.
As you mentioned, I have already completed this. Please refer to the snapshot for reference. Unfortunately,
it didn’t work.
image

Best Regards,
Vaibhav Narwade.