GROUPED DISPLAY IN BAR CHART

Question

Hi, everyone!

I have configured a dashboard where one of the elements is a bar chart of count of application status. Please see screenshot below.

However, I want to group these statuses to only 3 groups: Active, Approved, and Released in the DASHBOARD LEVEL. Please see screenshot of preferred output.

Although, I understand that adding another field for Status Group (where values are only Active, Approved, and Released) can solve this concern, but this solution is NOT an option since we are not allowed to add another field since the data is not ours and can only consume the layer view (non-hosted).

Is there a way to do this using expression? I appreciate any hints on this. Thank you very much.

kenbuja · Answer

Don't forget to click the "Accept as Solution" button on the post.

Have you added those fields to the FeatureSetByPortalItem and the GroupBy functions?

jantolihao · Answer

Hi, @KenBuja.

Thank you very much for this data expression. I was able to apply it on my dashboard.

However, I am having difficulty in adding my category selectors. I need to filter this bar chart by Application No. and Person Applied. Did you happen to encounter something like this before?

kenbuja · Answer

In a Data Expression, you can use the GroupBy function with a case expression in the SQL statement to return a FeatureSet with a calculated field. It would look something like this (not knowing how you want the original statues reclassified)

var fs = FeatureSetByPortalItem(
  Portal("your portal"),
  "itemID",
  0,
  ["Status"],
  false
);

var sql_statement = `CASE
WHEN STATUS = 'PENDING' OR STATUS = 'FINAL-PROCESS' THEN 'Active'
WHEN STATUS = 'FINAL-APPROVED' OR STATUS = 'PRELIMINARY-APPROVED' THEN 'Approved'
WHEN STATUS = 'SUCCESS' THEN 'Release'
ELSE 'Other'
END`

GroupBy(
  fs,
  { name: "Status", expression: sql_statement, statistic: "COUNT" },
  { name: "Total", expression: "1", statistic: "COUNT" }
);