jotg
March 14, 2023, 5:46am
1
Hi, after getting assistance with changing counts to percentage of total for a bar chart, I am now having trouble doing the same thing for a clustered bar chart.
This is what my visual looks like:
I then used this script to try and convert this to a %:
count({SwallowScreen_recode})/count(patientrecordid)
But putting this new calculated field in the value well, gave me this incorrect visual:
I couldn’t replace SwallowScreen_recode (in the group/colour well) with the new % calculated field, as it said “custom aggregation field is not allowed as a dimension”.
And if I remove swallowscreen_recode from the group/colour well, and just use %swallowscreen in the value well, my visual looks like this:
So I’m not sure what I’m doing wrong.
Thanks.
Max
March 14, 2023, 9:47pm
2
counts will count the row if it is not null.
Do you want to do distinct count?
distinct_count({SwallowScreen_recode})/distinct_count(patientrecordid)
jotg
March 14, 2023, 11:55pm
3
I do want to include the nulls. I recoded null to “Missing” using a calculated field.
Max
March 15, 2023, 2:07pm
4
Is distinct count what you are looking for? What does that give you?
Hi @jotg you are asking Percent of Total for the particular Month right?
jotg
March 21, 2023, 5:28am
6
Hi Max, I changed the calculated field to
distinct_count({SwallowScreen_recode})/distinct_count(patientrecordid), then added this field to the values well.
My visual now looks like this which is incorrect as for Jan 2021 yes should be 53%, no 36%, unknown 3% and missing 8% :
.
jotg
March 21, 2023, 5:29am
7
Hi @Biswajit_1993 Yes, that’s correct.
Hi @jotg , can you try with this below formula.
on the Measure column put your Measure field and the date part put your Date field.
for me I am getting the desired result by using tis formula.
Thanks & Regards
Biswajit Dash
1 Like
jotg
March 22, 2023, 1:52am
9
Hi @Biswajit_1993 , thank you. I used this script:
But there is some sort of error?
I’m also not sure why we’re using sum in the script rather than count?
Thank you for all of your assistance.
Hi @jotg can you put count in your case because in my case the field is different so I have take the sum but you can take as Count and check once.
jotg
March 22, 2023, 4:35am
11
Hi @Biswajit_1993 ,
I’m still getting an error:
I wonder if it’s because swallowscreen_record is a text field and date of admission is a date field?
Can you put one square bracket on the Date field.
Max
March 22, 2023, 8:03pm
14
It looks like you haven’t enclosed your percentOfTotal