I have 2 columns, 1 column for current period, and 1 column previous period.
I also have an option to specify year and month when generating the report.
how to I set the column to previous month? say when the current month is 1 (jan/2017), I need the previous mth to be dec/2016
thanks.
Date
Votes
3 comments
-
Jet Reports Historic Posts I use the EOMONTH Excel formula for most of my date conversions
= EOMONTH(start_date,months)
so if start_date is 1/1/2017, then months should equal -2 (to go back to the end of November), and then add one
assuming your current month value is in E6
=EOMONTH(E6,-2)+1 -
Jet Reports Historic Posts To add to Heathers statement, which is a fine method.
If I know my report will include only 2 periods to compare, but may have repeaters in my rows. To avoid having to do the date calculation for every repeated action, I would rather on my options page include the date calculation for last period, so you have:
Startdate: 01-03-2017
Enddate: 31-03-2017
Previous period start : =EDATE(startdate;-1)
Previous period enddate : =EOMONTH(startdate;-1)
I would also name your cells i.e. startdate & enddate for the actual period, and something simple like PPstart and PPend for the previous period.
Then in your repeaters you can simply use references to the values PPstart and PPend, thus saving some calculations in your repeater action.
There are many date functions that can get you this result, this is merely my method. You can quite easily use the =DATE(01;01;enddate-1) fx.
as a sidenote. I often use =DATE(01;01;'insert date') to calculate the startdate for my year-to-date intervals.
Happy Reporting :) -
Jet Reports Historic Posts thanks… will try it out.
Please sign in to leave a comment.