Two month periods and sum vs PY

[Anonymous]

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

Please, help!

[prieper]

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

[MK_QSL]

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

Data:

Load

  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)

Load Distinct

  MonthYear,

  AutoNumber(TwoMonthName,'TwoMonthName') as TwoMonthNameID,

  AutoNumber(TwoMonthNameCross,'TwoMonthNameCross') as TwoMonthNameCrossID

Resident Data

Order By MonthYear;

[Anonymous]

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.. !

[prieper]

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

Peter

[MK_QSL]

Can you explain ?

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

[Anonymous]

thanks, dear!!

Now i try to calculate sum for PY..

Could you please help me with it?

[Anonymous]

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

[MK_QSL]

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 !

[prieper]

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