This month or previous month in the Report
Hi,
I need would like to have reports where all the result are from "this month" or "previous month" but I don't see such filters in When? Option. Can someone help me with that?
Comments
-
you would need to add a helper column. That is a new column with an identifying formula that will post dependent on your criteria.
=if(month(@desiredcell)=month(today()),1,if(month(@desiredcell=month(today())-1,2)
Then sort/filter the criteria with 1 being the current month in your new column and 2 being the previous month.
-
That's great, but what happens in january at MONTH(TODAY))-1? There is no month 0... I ask because I'm trying to get a dashboard to show a running total of items completed in the last 12 months. Currently I'm having to go and manually update the dashboards (i.e. point to the new data). I want to automate!
-
Nevermind -
=IF(MONTH(TODAY()) - [Column19]30 > 0, MONTH(TODAY()) - [Column19]30, MONTH(TODAY()) - [Column19]30 + 12)
where column19 contains the number of months into the past. Still working on determinig year, but updating once a year isn't so bad.
-
A trick to getting around that is using dates instead of months.
=date(2019, 1, 1) - 1
would return 12/31/2018
so you could use the month formula on the date
=month(date(2019,1,1)-1)
would return a 12
You can use this to fully automate your equation, though it will make it significantly more complex/long
=month(date(year(today()), month(today()),1) -1)
-
The formula would look something like this: (untested)
if(netdays(@desiredcell,today())>360,"",
if(month(@desiredcell)=month(today()),1,
if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,
if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,
if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,
if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,
if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,
if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,
if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,
if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,
if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,
if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,
if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""
or collapsed:
if(netdays(@desiredcell,today())>360,"",if(month(@desiredcell)=month(today()),1,if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""
-
You can find the previous months number by using =MONTH(TODAY() - DAY(TODAY()))
so this will tell you if the test cell is in the previous month. if(MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row), 1, 0)
I used the following formula in a checkbox column to see if the date in my "Ship Scheduled" column is in the previous month. I added the 90 day limit to the first part of the AND statement to make sure that I am not getting false positives for dates more than 90 days in the past (e.g. 2,3,4 years ago, but the correct month)
=IF(AND(TODAY() - [Ship Scheduled]@row < 90, MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row)), 1, 0)
