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

[Tony_Insua]

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

[Suvrutt_Gurjar]

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)

[Tony_Insua]

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.

Any thoughts?

[Suvrutt_Gurjar]

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

[Tony_Insua]

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.

[Suvrutt_Gurjar]

Thank you @Tony_Insua for the updates.

Tony_Insua:

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.

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

Tony_Insua:

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.

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.