Limit number of item for charts

Ask for Help
1

Hey,

I’m building a workout app (callisthenic :mechanical_arm:)

When I’m done with the sets per exercice, I submit a form to enter the number of rep per set per exercice. Automatically, it registered the date.

Capture d’écran, le 2020-11-23 à 07.54.26
Capture d’écran, le 2020-11-23 à 07.54.262872×1403 215 KB

Once submit, results are compiled in a new tab (‘‘accomplissements’’ sorry french) in the Sheet and the app.

Capture d’écran, le 2020-11-23 à 07.55.14
Capture d’écran, le 2020-11-23 à 07.55.142880×1401 297 KB

I also add a chart in the exercice page (see in this example the ‘‘Push-up (incliné)’’ page). The chart show the number of reps for the last days. So I know how many reps minimum I have to do and be better every time.

BUT (here’s the question lol), I want to only show 7 days maximum. Because if I show 8+, the chart is too messy and I can’t see anything.

Capture d’écran, le 2020-11-23 à 07.55.58
Capture d’écran, le 2020-11-23 à 07.55.582880×1413 365 KB

I tried everything but I can’t figure out the right filter to use.

Thanks in advance!

2

In the data, create a math column that takes the date and adds 7. Then add a filter to the chart that will only show data when the math column is “On or After” Today.

1 Like
3

Like that?

Capture d’écran, le 2020-11-23 à 08.32.31
Capture d’écran, le 2020-11-23 à 08.32.312880×1405 369 KB

Capture d’écran, le 2020-11-23 à 08.33.12
Capture d’écran, le 2020-11-23 à 08.33.122880×1407 263 KB

It doesn’t work.

I want to add the info that I don’t workout each day so it’s not ‘‘Last 7 days’’. Maybe the 7th days is one month ago.

4

Probably you would need a custom ranking formula in the sheets to rank the days and then display the count of max items for your profile minus 7.

1 Like
5

Yes I thought about that but I don’t know what formula to use. I’ll dig, thanks!

6

You do need to rank by each user, is that correct? I might have a custom function to help you in the Sheets.

7

For now, the app is made for one user (the creator). There is no Row Owner yet. No sign in!

8

So can you please try this and let me know if it helps? I’ll jump in if you need further assistance.

1 Like
9

It works! In fact, I can do it manually but my arrayformula does not seem to work.

Here’s the formula if I do it manually with for example 9 dates.

=RANK(A2,A$2:A)

Capture d’écran, le 2020-11-23 à 13.04.19
Capture d’écran, le 2020-11-23 à 13.04.192880×1405 315 KB

but If I want that the formula work on a new value added in the app, I would use something like

=ARRAYFORMULA(IF(A2:A="","",RANK(A2,A2:A)))

And it doesn’t work… what’s the issue?

Capture d’écran, le 2020-11-23 à 13.04.35
Capture d’écran, le 2020-11-23 à 13.04.352880×1396 316 KB

Here’s the filter in the chart… that part works just fine :slight_smile:

Capture d’écran, le 2020-11-23 à 13.05.04
Capture d’écran, le 2020-11-23 à 13.05.04898×531 18.4 KB

10

Salut franck
Pour une fois qu’il y a des Français on ne va pas se priver :grinning:
Le probleme dans ton arrayformula c’est dans rank tu as déjà une matrice A2:A. Je te conseille de nommer la plage A2:A
Ta formule sera alors
=arrayformula(si(A2:A=“”;””;rank(A2:A;plage nommée))

1 Like
11

Salut! Oui on va parler en français :smiley:

=arrayformula(si(A2:A=“”;””;rank(A2:A;plage nommée)))

Je ne suis pas certain de comprendre pour rank.

rank(value, data)

Value, tu me dis de mettre A2:A ?
Data, tu me dis de mettre quoi ?

12

Oui dans arrayformula une cellule devient une plage de cellule et donc ton probleme c est qu’ensuite ton data qui est déjà une plage ne peut pas changer de dimension d’où la nécessité de nommer la plage.

Pour être clair
Sans arrayformula
= si(A2=“”;””;rank(A2;A2:A))

Avec arrayformula
= arrayformula(si(A2:A=“”;””;rank(A2:A;plage nommée)

Et dans Google sheet tu vas dans le menu données et plages nommées.
J’espère que c’est plus clair :wink:

Regarde le support de Google sheet pour les plages nommées. C’est l’astuce normalement.

13

Merci! Ca semble bien marché avec la formule =ARRAYFORMULA(SI(A2:A="","",RANG(A2:A,A2:A)))

Maintenant, j’ai un autre problème que j’avais oublié. Je dois classer les rank par exercices. Par exemple, j’ai ai 10 exercices ‘Push-up (incliné)’, jusque là ça va.

Capture d’écran, le 2020-11-23 à 15.32.59
Capture d’écran, le 2020-11-23 à 15.32.592867×1406 305 KB

Mais si j’ajoute un autre exercice, le rank ne voit pas la différence.

Capture d’écran, le 2020-11-23 à 15.33.46
Capture d’écran, le 2020-11-23 à 15.33.462880×1397 323 KB

Comme le nouvel exercice se retrouver #1. Il me reste 6 exercices dans mon chart :upside_down_face:

Capture d’écran, le 2020-11-23 à 15.35.00
Capture d’écran, le 2020-11-23 à 15.35.002880×1385 340 KB

Il y a un moyen d’ajouter une fonction ‘par catégorie’ dans la formule?

14

Oui tout à fait … we can switch to English :wink: this is easier to explain.

You can use the filter function inside the rank function.
But you would need to add a value to know which exercice to filter.
To do so you would need to have a specific column for each exercice and the name of the column would be the value in the filter. This solution should work but not very Good in the long term.

1 Like
15

Oh I understand. What would be the best solution in the long term?

16

So has the situation been solved? I can share my script if the original Rank doesn’t work.

17

Yes it worked for all exercices using =ARRAYFORMULA(SI(A2:A="","",RANG(A2:A,A2:A)))

Now, I need to filter them. Because, if I have, for example, 7 ‘‘push-up’’ and I add ‘‘2 abs’’, it will count as 9 push-ups instead of 7 so the rank will not be right.

I checked the filter formula but it’s more complicated now if I have to add new columns for each exercice like @Marc-Olivier explained.

1 Like
18

Ok I found the solution. Not the easiest but it works.

As each exercice is different, I will create a different screen configuration in my inline list.

Capture d’écran, le 2020-11-23 à 21.24.14
Capture d’écran, le 2020-11-23 à 21.24.142880×1404 209 KB

Then on my sheet, I will create a tab per exercice. When I submit the form after each set, it will link to their tab.

I wish there was a way to at least copy a screen configuration so I don’t start from scratch each time :sweat_smile: (20 screens to do!).

Thanks all, you helped me with the rank formula :fire:

19

So you need a ranking formula that lives in the same sheet with multiple types of work out challenges and it needs to be ranked based on each work out type, right?

20

Exactly! If you find it, you’re the best!