Calculate average on gallery records

[SteveDK]

Hi,

I have 2 questions about a gallery to show in my app. My app is to track time on a physical check of items.
I'm tracking the CheckTime for the 10 first times an item is checked.

Is there a way I can show a gallery with the checked items (only showing the Title once) and also show the average of that item 10 times check records? If possible to calculate the average even if that item isn't checked for 10 times yet?

My sharepoint list is build as example below.

Any suggestions are welcome.

Thank you,
Steve

[v-yangar-msft · Accepted solution]

Hi @SteveDK ,

Based on your screenshot of your SharePoint List, I created a same list as yours:

Here I use CheckIngTime instead of CheckTime because they are both calculated column and don’t have the value of second.

For your first issue, you can set the items  property of gallery to:

Distinct(TimeTrack,Title)

And it will distinct the duplicated record and only show the Title once.

For your second issue and third issue, firstly, you should get two values: the amount of checked items and the total of checking time.

Add a label and set the Text property to:

  Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)

//This formula aims to get the average of checking time

Finally, we need to format the value of average to “mm:ss” and set the Text property to:

Concatenate(First(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,

                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result,":",Text(Value(Last(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result*6)))

Hope it helps!

Thanks,

Arrow

[SteveDK]

Hi,
@v-yangar-msft 

Thank you for your reply.

So what I did is to set my CheckTime column calculated formula in my Sharepoint list to just aim for hh:mm        =TEXT(StopTime-StartTime,"hh:mm")
because it had the seconds value included in the formula and didn't show me any outcome.

Then added your formula to calculate the average to my gallery in a text label and adapted the list name and column name to my settings but received an identic calculation for each item in my gallery and doesn't seem to show me the correct outcome. For example the outcome for the first record on the below print screen should be 4.
I think i'm still overlooking something 🤔

Kind regards,

Steve

[Drrickryp]

HI @SteveDK 

To get the hrs and minutes from the DateDiff() is not as simple as it might seem @CarlosFigueira  came up with this formula.  You use DateDiff() twice, once for hours and once for minutes. 

DateDiff(
    dtStart.SelectedDate + Time(Value(ddHourStart.Selected.Value), Value(ddMinuteStart.Selected.Value), 0),
    dtEnd.SelectedDate + Time(Value(ddHourEnd.Selected.Value), Value(ddMinuteEnd.Selected.Value), 0),
    Hours) &
":" &
Mod(
    DateDiff(
        dtStart.SelectedDate + Time(Value(ddHourStart.Selected.Value), Value(ddMinuteStart.Selected.Value), 0),
        dtEnd.SelectedDate + Time(Value(ddHourEnd.Selected.Value), Value(ddMinuteEnd.Selected.Value), 0),
        Minutes),
    60)

[v-yangar-msft]

Hi @SteveDK ,

If you have further questions, you can post a new case and we can continue to help you in this hand.

Thanks,

Arrow

[SteveDK]

Hi,
@v-yangar-msft 

With some adaptions to personal needs this finally works for me.
Thank you very much for your help.

Kind regards,

Steve