Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
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.. !
yep - overlooked that you wanted to have it a year backwards. Just adjust the formula and should work then
Peter
Can you explain ?
Field TwoMonthNameCross get dates only for first month of every pair:
thanks, dear!!
Now i try to calculate sum for PY..
Could you please help me with it?
Oct'15-Nov'15- Here must be dates for Oct'15 and Nov'15, not only for Oct'15
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 !
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