9 Replies Latest reply: Feb 3, 2016 6:02 AM by Peter Rieper

# Two month periods and sum vs PY

Colleagues, good day!

Help me please to create several fields:

1. TwoMonthName (Jan'14-Feb'14, Mar'14-Apr'14, May'14-Jun'14, Jul'14-Aug'14, Sep'14-Oct'14, Nov'14-Dec'14,Jan'15-Feb'16)

2. TwoMonthNameCross (Jan'14-Feb'14, Feb'14-Mar'14, Mar'14-Apr'14, Apr'14-May'14, May'14-Jun'14, Jun'14-Jul'14, Jul'14-Aug'14, Aug'14-Sep'14, Sep'14-Oct'14, Oct'14-Nov'14, Nov'14-Dec'14,Dec'14'-Jan'15, Jan'15-Feb'15...)

Also i need to calculate sum for this periods in expr (in pivo table), and vs PY.

There's example in source file on sheet Pivot

For TwoMonthNameCross expr PY must be calculated by the same logic, but for period, which include months from two Years (Dec'14-Jan'15, Dec'15-Jan'16.. ) PY must calculated by following logic:

Dec'14-Jan'15: Dec'14-Jan'15/ Dec'13-Jan'14

Dec'15-Jan'16:Dec'15-Jan'16/Dec'14-Jan'15

• ###### Re: Two month periods and sum vs PY

You may use INTERVALMATCH, see the attached example

... might be finetuned with JOIN, but think that you should be able to see the logic behind.

HTH Peter

• ###### Re: Two month periods and sum vs PY

Unable to understand your requirements but you can create below fields in your script.

Data:

Date(Date) as Date,

Mod(Month(Date),2) as Mode,

Date(MonthStart(Date),'MMM YY') as MonthYear,

If(Mod(Month(Date),2) = 1, Date(MonthStart(Date),'MMM YY') & '-' & Date(MonthStart(Date,1),'MMM YY'),      Date(MonthStart(Date,-1),'MMM YY')&'-'&Date(MonthStart(Date),'MMM YY')) as TwoMonthName,

Date(MonthStart(Date),'MMM YY') & '-' & Date(MonthStart(Date,1),'MMM YY') as TwoMonthNameCross,

Sales

From TableName;

Left Join (Data)

MonthYear,

AutoNumber(TwoMonthName,'TwoMonthName') as TwoMonthNameID,

AutoNumber(TwoMonthNameCross,'TwoMonthNameCross') as TwoMonthNameCrossID

Resident Data

Order By MonthYear;

• ###### Re: Two month periods and sum vs PY

Many thanks!

Try your solution in my model..

Field TwoMonthNameCross get dates only for first month of every pair:

Oct'15-Nov'15- Dates only for Oct'15

Dec'14-Jan'15 - Dates only for Dec'14.. !

• ###### Re: Two month periods and sum vs PY

yep - overlooked that you wanted to have it a year backwards. Just adjust the formula and should work then

Peter

• ###### Re: Two month periods and sum vs PY

thanks, dear!!

Now i try to calculate sum for PY..

• ###### Re: Two month periods and sum vs PY

Would than go the usual way:

Create a rolling calendar in the form

Year * 12 + NUM(Month)     AS RollingMonth

PY (and other datecalculations) then can be done easily by deducting 12 from the RollingMonth, as you have some fine numeric values to refer to

Peter

• ###### Re: Two month periods and sum vs PY

Can you explain ?

Field TwoMonthNameCross get dates only for first month of every pair:

• ###### Re: Two month periods and sum vs PY

Oct'15-Nov'15- Here must be dates for Oct'15 and Nov'15, not only for Oct'15

• ###### Re: Two month periods and sum vs PY

Let me clear ...

January 2014 - you want to display as Jan 14 - Feb 14

February 2014- you want to display as Feb 14 - Mar 14

But when you select Jan 14 - Feb 14, you will get data of both.. Jan 14 and Feb 14.

Please correct if I understood wrong !