How to Solve: Histogram Chart (Horizontal Bar Chart) from a SLICE

Not sure how to solve this. I’ve created a slice (shown below) that shows ACTIVITIES and the DURATION of those activities.

I want to show the TOTAL DURATIONS per activity but the best I can do is get the COUNT (# of times the activity was listed). I’d like to show the X axis to include the TOTAL DURATIONS (TIME) and the Y axis as the independant activities.

Can anyone point me to the right direction in how to set this properly?

Thank You

Please explore following

In order to add durations for each activity , you may need to convert those to decimal durations. So 01:00:00 hour is represented as 1.00 hour , 00:30:00 hour is represnted as 0.5 hour , 00:45:00 is represnted as 0.75and so on.

For this, please add a VC called say DecimalDurations with an expressions as TOTALHOURS([Duaration])

Now please base your histogram on Chart column as [ACTIVITY] and group aggregate as SUM : DecimalDurations ( Settings available in UX -> View -> Chart Type View settings)

3 Likes

Hi Suvrutt,
I’ve created the new Virtual Column as DecimalDurations, which seems to be have worked properly (see below)

For the Histogram Chart, I’ve grouped as [ACTIVITY] however there is no option to group aggregate as SUM : Decimal Durations. See screenshot below. Looks like “COUNT” is the only option.
Screen Shot 2020-06-15 at 10.13.56 AM

Any thoughts?

Hope you have regenerated the table and synched the app after adding the new column and filling in values. I am able to get the SUM aggregate for DecimalDurations

3 Likes

HI Survutt,
Got it to work! So yes, I had synced the app and regenerated the table before. The issue was I had set the VIRTUAL COLUMN for DECIMALDURATIONS to NOT SHOW as I don’t want to see this value in my table. The group by aggregate SUM :: DECIMALDURATIONS does not show if the SHOW is off. When I toggled the DECIMALDURATIONS to SHOW, and then went back to the UX > CHART > GROUP AGGREGATE the SUM :: DECIMALDURATIONS appears. This is interesting, not sure if you see the same thing?

There may be a hack to this. If I SHOW the column, then go to the UX and group aggregrate by SUM :: DECIMALDURATION, then go back to the column and turn of SHOW, it looks like the chart still remains SUM :: DECIMALDURATION.

Separately, I think it would be more beneficial if the X axis was not a decimal number and rather in the 00:00:00 format. It’s hard for a user to know how many hours and minutes and seconds 6.25 is (for example). Wonder if this is just an AppSheet capabilitiy issue of not being able to show 00:00:00 in the x-axis.

Thank you @Tony_Insua for the updates.

Great to know this hack works. Other alternative may be to use CONTEXT(“ViewType”)= “Chart” in the show if constraint of the column [DECIMALDURATION]

Yes, true. However, as we experienced, both the Time type and Duration type columns do not appear in aggregate function in the chart view settings. So we needed to go for the decimal time workaround.

2 Likes