Filter data by month

Telpro
Participant II

Im new here… thanks before helping me…

i have data from jan 2020, buat i wanna filter by month, they only show this month data…
how to do that ???

thanks for helping me…

0 27 3,472
27 REPLIES 27

hru
Participant V

Hi @Telpro, if I understand correctly from your short explanation above, your intention is to show this month data from your Data table.

If that is true, you may want to to create a Slice from the DataTbl and use below expression at the “Row filter condition”:

MONTH([YourDateColumn])=MONTH(TODAY())

I would suggest this expression instead:

EOMONTH([YourDateColumn], 0)=EOMONTH(TODAY(), 0)

The EOMONTH() function provides the date of the last day of a month, whereas MONTH() just gives the month number. April 2019 and April 2020 are both month 4, but clearly different months. Using EOMONTH() avoids that.

See also:

Thanks @Steve for the correction, I did not consider about the “Year”… still learning…

Telpro
Participant II

ok guys thanks for ur help,

nah can i filtered it by week ? i mean number of week in every month…

It will be depend on what you want to achieved. Is it every week show different data for that week or you want to have a weekly tabulation of data view or some other things?

i wanna add new tab that show week number for my data, what is the best solution for this ???

What do you mean by “new tab”?

Telpro
Participant II

sory, i feel still using sheet,
i mean new view, so in that view i can show week number of the month, is that possible ?

hru
Participant V

To show week number of the month you can use the WEEKNUM() expression. It will depends on your needs, you might want to use a virtual column or a new column in your table using the App Formula.

However, If you want to create a slice for weekly view (in addition to monthly view), you can use this:

WEEKDAY([YourDateColumn]) = WEEKDAY(TODAY())

And you might want to take a look at below help article:

thanks guys, im gona try it…

WEEKNUM() is going to have the same problem MONTH() had: it can’t differentiate years. And again, the fix is to find a common date. The following will tell you if the YourDateColumn column value contains a date in the same week as today:

((TODAY() - WEEKDAY(TODAY())) = ([YourDateColumn] - WEEKDAY([YourDateColumn])))

WEEKDAY(some-date) gives a number (1 to 7) indicating the day of the week (Sunday to Saturday) on which the given date falls. Subtracting that weekday number from that same date gives the date of the date of the last day of the preceding week (the preceding Saturday). Two dates that have the same last day of the preceding week are in the same week.

See also:

Thanks, Steve.

Wouldn’t it better to also include you response like this to the help article? Without your reminder, other like me will just “think short”.

While we are on this WEEKDAY(), I am a bit confused with the Return Value from the article as below snapshot:

If Contains recognizable date, Sunday is 1 other time Sunday is 0, what is recognizable date?

Recognizable dates:

  • TODAY()
  • "3/26/2020"
  • DATE([DateTimeColumn])

Not recognizable dates:

  • ""
  • 4200
  • TIME([DateTimeColumn])

In other words, WEEKDAY() returns 0 if given invalid input. Maybe I should just put that…

Done.

You can complete updating the help article before I could ever finish my thank-you response to you…

Thanks again, now it is very clear to me.

Telpro
Participant II

Nah im confuse guys

Now whats the method to use ?

((TODAY() - WEEKDAY(TODAY())) = ([YourDateColumn] - WEEKDAY([YourDateColumn])))

its for show week number right ? how to show last week filter then?? is that possible

for my reference pls add for today too…

thanks for ur help sir,

Please describe what you’ve tried so far with the given expressions and let us understand your difficulties for us to be more helpful for you.

I want to :

  1. Show Only This Month Data
  2. Show Only Last Week Data
  3. Show Only Today Data

i need this to show on my summary , and make the chart…

hru
Participant V

Please try the expression above as responded by Steve, and let us know the result.

  1. succes ( @Heru Method)
  2. when i put expression on Slice (@Steve Method), it showing this week, I need to show last week
  3. still no answer yet

For item#1 Better to try what was suggested by Steve as it considers also for change of year.

For item#2, to show last week, please try this:

WEEKNUMBER([YourDateColumn])=WEEKNUMBER(TODAY()-7)

For Item 3, to show data of today:

[YourDateColumn] = TODAY()

Telpro
Participant II

Ok thanks guys, really helpful,

for item no.2 i only can use WEEKNUM, error when i input WEEEKNUMBER

Yups, sorry for that, I also need to be more familiar with expressions.

However, to consider for the year, you might want to try below expression:

AND(
	WEEKNUM([YourDateColumn])=WEEKNUM(TODAY()-7),
	YEAR([YourDateColumn])=YEAR(TODAY()-7)
)

Let me know.

Telpro
Participant II

Its working bro, thanks for the help

hru
Participant V

You are welcome, thanks for getting back and confirming. Hope you’ll have good progress with your apps.

Top Labels in this Space