How to change the data of a chart according to the selected date?

I have information that is classified by date and I want to present the data according to it in a graph to represent the history to the selected day. How can I control that? When I put the component to select a date, doesn’t it link it to the table where the data is?

Thank you very much for your support

I think you can use a QUERY to achieve that. Have not used a Date component in my apps, do that store a value in the sheet?

If yes, you can probably do something like: QUERY(your original full data, “SELECT * WHERE DateCol = '”&(Insert Cell Where Date component value is stored)&"’,-1)

Then get the chart based on the newly queried value.

Hope it helps.

1 Like

You should be able to do this with a relation. Create a sheet that will contain a description column or anything you want really. Doesn’t matter. That new sheet will also become your tab displayed in the detail style view. You can also create a date column in the sheet, but preferably you would create a column within the Glide data tab as a date column, but make it user specific so this works for multiple people. Set your date picker to fill this date column. Now create a relation column that will link the date in this sheet to the date in your chart data sheet. The relation is what you will use for your chart component. This will allow you to select any date and the relation will pull back any chart values the match that date. This will work instantly without any lag.

2 Likes

Many thanks!!

1 Like

Many thanks, lets try this

1 Like

If you have any problems with it feel free to contact me via this thread or at: ariesarsenal@gmail.com.

1 Like

Thanks, I´ll do

1 Like

I already found the solution for this problem and was using this SQL sentence

=“Select C ,B, E,sum(E) WHERE C = date '”&text(K4,“yyyy-mm-dd”)&"’ group by C,E, B"

I made this example so you can see how it works

this is the link Change chart according the selected day

Its a little slow but works :slight_smile:

**dates started on 4/22/20

Many thanks for your help!!!

1 Like

Hola Juan,

Thanks for sharing your app. I am trying to build up an app that would store user´s health self-measurement. I am struggling with replacing the data picker in your app by a choice component.

With that choice component the user can select only dates for which he or she has submitted self-measurement values

I am new to glide. Could you please provide me some hint and also, clarify, how did you managed to get the value of the date picker as the filter condition of the data?

Thank you

Hi Antonio, firstly welcome to Glide!

Can you share with us some screenshots about your problem, and your current settings for the app?

As for the last question, I would think that the date picker’s value goes to a certain cell in his sheet, then it will be passed on to the QUERY presented here.

Hi ThinkDinh.

Thanks for the welcome and reviewing my question. Please, have a look at the screendhots at this link

https://share.getcloudapp.com/collections/gKfJ3d/Glide-apps-community

Indeed, the date picker´s value goes to a certain cell in his sheet. Unfortunately, I can´t figure out the process to put that value there.That, on the one hand. I woud actually need to pass to the Query two parameters: date from the date pick up, and user (which is the user´s email).

Best regards

I might need to see more of how your data is structured and how to dates are stored, but you should be able to select a date from a choice component, then create a template that joins the user’s email and the selected date, the create a relation to the sheet that contains the data for the chart. In the chart data sheet, you should have a similar template of email and date. Then you can use the relation you created for the source of the chart.

1 Like

Thanks, Jeff,

THIS IS AN UPDATE

I managed to achieve the result, following Jeff´s advice posted on How to change the data of a chart according to the selected date?

The only thing that I now struggle with is having the date choice component display dates sorted I would appreciate any help on this.

The image shows the table that has all the data.

image

It would be straightforward to have a method to use the chosen date as a parameter for the chart filter. Obviously, that is not the way glide works. :thinking:

Hi Jeff, I have posted in the thread two answers to your message. With this one I also want to provide an update. I know start to understand that when you mentioned template you were referring to creating template columns. Now I am doing some progress following your advice. Could you please have a look at my other message where I show the data structure, as you adviced?

I’m assuming that you are building your choice component from the measurment response sheet?

You can apply sorting to a choice component under the feature tab in the component’s settings. The problem you will have though is that the date looks to be stored as text instead of a date and since you are storing it in dd/mm/yyyy format, it’s going to sort by day first. You could either changed your dates to be stored in yyyy/mm/dd format or create another column in your sheet and use a formula to reformat the date in yyyy/mm/dd. Then use that extra date column for sorting, but still use the dd/mm/yyyy format for your relation.

Thanks a lot Jeff,

Yes, I am building the choice component on the measurement table. Now the date choice is working very well. I am worried about the fact that my spreadsheet in Google doesn’t seem to be synchronizing with the app. I deleted errors in the spreadsheet which are still in the data and the data that I put to the app works perfectly, but is not.

I recorded a video

https://share.getcloudapp.com/NQuD95x9

Are you using arrayformulas? Did you check the bottom of the sheet?

Yes. I am using one array formula and all the records present in the app and not in the spreadsheet are actually in rows 974 and following, as you suspected. Would it be safe to delete in the app all the bunch of blank rows?

1 Like