Adding time variable to graphs

[Gera_Yeremin]

I am not sure if this is possible yet but I want to add a chart that shows data for the last 30 days vs the lifetime of the info.

Is there a way to make the data time-sensitive.

[Ryan_Nisbet]

I’d love this

[ThinhDinh]

Hi Gera,

As of now I don’t think this is doable just inside Glide, but here’s a workaround.

I have some random data like column A and B, then in column C I put this in the first cell.

={"Date type";ARRAYFORMULA(IF(A2:A<>"",IF(A2:A>=TODAY()-30,"Within 30 days","Not in last 30 days"),""))}

It would result in something like this.

Then you can make a chart with it, with the lifetime total value showed in the center.

Hope it helps.

[Gera_Yeremin]

@ThinhDinh

={"Date type";ARRAYFORMULA(IF(A2:A<>"",IF(A2:A>=TODAY()-30,"Within 30 days","Not in last 30 days"),""))}

You are the MAN! This is super helpful. For some reason, I am getting #REF! Error though. Am I stuck writing a complex formula for this or can I break it out a bit?

Here is the end game:
Reviews Today
Reviews Yesterday
Reviews Over The Last 7 days
Reviews Over The Last 14 days
Reviews Over The Last 30 days

Reviews This Month
Reviews Last Month
Reviews in 2020
Reviews in 2019

[Gera_Yeremin]

@ThinhDinh
Update:
I got the formula working for some reason though when I turn it into a chat it gives me some bogus numbers. It works in pie chart mode. If I wanted to add a bunch of my variables how would I edit your original formula?

By the way you are a boss and this was super helpful.

[ThinhDinh]

How many variables do you want to add? It would be complex, but it’s doable if we get the right logic.

[Gera_Yeremin]

Currently, I am looking at 9.

Here is how far I have gotten it.
={“Date type”;ARRAYFORMULA(IF(
E2:E<>"", E2:E<>"",IF(E2:E>=TODAY()-30, ,IF(E2:E>=TODAY()-7,
“Within 7 days”,“Within 30 days”,“Not in last 30 days”,“Not in last 7 days”)
,""))}

What is " E2:E<>" doing?

[ThinhDinh]

That is part 3.3 in my writeup here.

Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job.

1/ Definition

Arrayformula is defined as “a range, mathematical expression using one cell range …

[ThinhDinh]

Hi Gera, based on the variables you have listed out I have made a sheet for you. I think it would be better to divided it into 4 columns:

• Recent: Yesterday & Today
• By days: Last 7, 14 and 30 days
• By month: This month & Last month
• By year: This year & last year

You can view the formulas in the header row of each column.

ThinhDinh's Glide Lab

[Erni_D]

looking around to learn some more abouth this feature

[ThinhDinh]

Related to the use of date to filter a chart, if you’re interested.

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 [grafica]

[Gera_Yeremin]

@ThinhDinh You are a data modeling wizard! Thank you for putting the functions together for me.

[ThinhDinh]

My pleasure to help, feel free to put more questions if you need help, here or via personal message!

[Gera_Yeremin]

@ThinhDinh if you like I can show you the app I am working on in Glide it’s really cool and uses data I am gathering from another application and builds a back end around it. I would love your feedback on how I can make it even better.

[ThinhDinh]

Feel free to show it here, I would love to learn from you and others as well so don’t hesitate to do it.

[Gera_Yeremin]

Hey! I used some of your code and it seemed to work like a charm.
={“Recent”;ARRAYFORMULA(IF(E2:E=TODAY(),“Reviews today”,IF(E2:E=TODAY()-1,“Reviews yesterday”,"")))}

This give me the last 2 days based on a date in another field. For some reason it stopped working though. Any idea whats up? Maybe I broke it somehow.

[ThinhDinh]

What is it returning now?

[Gera_Yeremin]

Currently nothing. I am not sure what happened because it worked at some point.

[ThinhDinh]

Share with me the sheet, I will take a look to see what happens

[Gera_Yeremin]

What’s your email?