Query on Heat map, changing axis

I am creating a heat map based on two category fields(calculated fields), The plot comes correctly but I want to show null pairs also, eg when data is not present for particular pair categories, it does not display the categories in the Axis, I want it to at least show category pair and show value 0.

‘BMI category’ calculated on the User’s BMI
ifelse(
BMI > 18 AND BMI <= 20, ‘[18, 20]’,
BMI > 20 AND BMI <= 22, ‘[21, 22]’,
BMI > 22 AND BMI <= 24, ‘[23, 24]’,
BMI > 24 AND BMI <= 26, ‘[25, 26]’,
BMI > 26 AND BMI <= 28, ‘[27, 28]’,
BMI > 28 AND BMI <= 30, ‘[29, 30]’,
BMI > 30 AND BMI <= 32, ‘[31, 32]’,
BMI > 32 AND BMI <= 34, ‘[33, 34]’,
BMI > 34 AND BMI <= 36, ‘[35, 36]’,
BMI > 36 AND BMI <= 38, ‘[37, 38]’,
BMI > 38 AND BMI <= 40, ‘[39, 40]’,
‘Above 40’
)

‘Risk Score’ category based on ‘Avg Risk Score’ field of User.

ifelse(
{Avg Risk Score} > 0 AND {Avg Risk Score} <= 10, ‘(0, 10]’,
{Avg Risk Score} > 10 AND {Avg Risk Score} <= 20, ‘(10, 20]’,
{Avg Risk Score} > 20 AND {Avg Risk Score} <= 30, ‘(20, 30]’,
{Avg Risk Score} > 30 AND {Avg Risk Score} <= 40, ‘(30, 40]’,
{Avg Risk Score} > 40 AND {Avg Risk Score} <= 50, ‘(40, 50]’,
{Avg Risk Score} > 50 AND {Avg Risk Score} <= 60, ‘(50, 60]’,
{Avg Risk Score} > 60 AND {Avg Risk Score} <= 70, ‘(60, 70]’,
{Avg Risk Score} > 70 AND {Avg Risk Score} <= 80, ‘(70, 80]’,
{Avg Risk Score} > 80 AND {Avg Risk Score} <= 90, ‘(80, 90]’,
{Avg Risk Score} > 90 AND {Avg Risk Score} <= 100, ‘(90, 100]’,
‘No Data’
)

When I plot a Heap map based on these values, the heat map looks like the below

But I want to chart to be like

Is there a way to achieve this?

Thanks and Regards,

1 Like

Hi @AjayV ,

Thanks for raising this I have been facing the same challenges.
Please try this simple calculation and let me know if it works for you.

First replace the BMI Field with the below calculations.

image

Second replace the Avg Risk Score Field with the below calculations.

image

Once you complete it, then create your calculation.

Thanks & Regards
Biswajit Dash

Thanks, Biswajit, I tried this but it was not exactly what I was asking, The solution you have provided will place the value 0 instead of null in the Avg Risk Score. The thing I was taking was if no user’s value falls in the particular category when I plot, that category is not visible in Axis. I want to show all the categories in the Axis even if users are not in that category and give a value of 0.

eg - see the below formula, if none of the User’s Avg Risk Score Value falls in the [0, 10] or [70, 80] category, when I use Risk Score category field in X-Axis, the labels [0, 10] or [70, 80] will not be displayed as No users will be in this category ( same case in the BMI category). But I want to display all categories. in the Axis labels, So I am asking is there a workaround using parameters or other things.

Calculated field ‘Risk Score category’ based on ‘Avg Risk Score’ field of User.

ifelse(
{Avg Risk Score} > 0 AND {Avg Risk Score} <= 10, ‘[0, 10]’,
{Avg Risk Score} > 10 AND {Avg Risk Score} <= 20, ‘[10, 20]’,
{Avg Risk Score} > 20 AND {Avg Risk Score} <= 30, ‘[20, 30]’,
{Avg Risk Score} > 30 AND {Avg Risk Score} <= 40, ‘[30, 40]’,
{Avg Risk Score} > 40 AND {Avg Risk Score} <= 50, ‘[40, 50]’,
{Avg Risk Score} > 50 AND {Avg Risk Score} <= 60, ‘[50, 60]’,
{Avg Risk Score} > 60 AND {Avg Risk Score} <= 70, ‘[60, 70]’,
{Avg Risk Score} > 70 AND {Avg Risk Score} <= 80, ‘[70, 80]’,
{Avg Risk Score} > 80 AND {Avg Risk Score} <= 90, ‘[80, 90]’,
{Avg Risk Score} > 90 AND {Avg Risk Score} <= 100, ‘[90, 100]’,
‘No Data’
)
Please see the plot in Question, In the first plot shows only 4 Risk Score categories, as users belong to only that 4 categories, but I want to display all the categories and show a value of 0 as in the Second Plot.

Thanks and Regards,
Ajay Venkatesh

HI @AjayV ,
Thank you for your confirmation. I will check and get back to you

Thanks & Regards
Biswajit Dash

Hi @Biswajit_1993 , kindly let me know if you have solution for this.

1 Like

Hi @AjayV ,
Today is a busy schedule, so I’m not able to analyze the details.

Thanks & Regards
Biswajit Dash

Hi @Biswajit_1993 , do you have any suggestions for the query, or can you clarify if it is possible or not, if you still have a busy schedule can you kindly assign it to other members of the support team?

Thanks and Regards,
Ajay V

Hi @AjayV

This is a really good scenario. Let me try it and get back to you. Can any of our expert members share some logic to build this?

Tagging @duncan , @Giridhar.Prabhu , @David_Wong, @prantika_sinha, @Sanjeeb2022 to share there expertise on this topics.

I do not think this is achievable through calculations in analysis. Considering if there is no user under the category, ideally there is no record (row entry) in the underlying derived table for the category. Also, the category being interpreted as discrete value in heatmap axis, the category cannot get populated like a series.

However if this calculation if done in data backend, we may be able to add a record for each category. Feasibility depends if the measures used to determine category are not calculated fields.
For example:
step 1: create calculated field in dataset to determine category
step 2: create a csv with required field names only
step 3: perform a dataset join (full outer), that creates at least one record for the category on the right table (csv) with all values on the left table kept unchanged.
step 4: hide category calculated field from left table, to avoid confusion.

The challenge is this step will not work, if the category is encoded using nested calculations.

2 Likes

Sure! Here’s a more polished version of your message:


Hi @Prantika_Sinha,

Thank you for sharing the detailed steps.

Hi @AjayV, could you please review and implement this logic, and let us know if it’s possible or not?

Thanks & Regards,
Biswajit Dash

thank you @Biswajit_1993 and @prantika_sinha I will try and update you soon.

Thanks and Regards,
Ajay V

1 Like

Hi @AjayV,
It’s been awhile since we last heard from you, did you have any additional questions regarding your topic or did one of the suggestions above aid in your solution?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @AjayV,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information if needed.

Thank you!