Prev Month Sum

[dmac1971]

I simply cannot get this to work, am scratching my head!!

This works fine :

sum({<Month={"Dec"}, Year = {"2014"}>}[Qty])

However anything else I try wont.  Here is my Calendar Script :

TempSalesOrderMinMax:
Load min(SalesDateID) as MinDate,
max(SalesDateID) as MaxDate
Resident [Spares Sales]
;

Let vStartDate= Peek('MinDate');
Let vEndDate=Peek('MaxDate');
Let vNumDays=vEndDate-vStartDate+1;

drop table TempSalesOrderMinMax;

Calendar:
Load
TempDate as SalesDateID,
Date(TempDate) As Date,
Year(TempDate) As Year,
Year(TempDate) & '-' & Month(TempDate) As MONTH_YEAR,
Month(TempDate) As Month,
num(Month(TempDate)) As MonthNum,
'Q' & Ceil(Month(TempDate)/3) as Quarter,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) As Q_YEAR,
NetWorkDays(TempDate,TempDate) as NetworkDay_Flag,
Day(TempDate) As Day,
Week(TempDate) As Week,
WeekDay(TempDate) As WeekDay,
if(((Year(TempDate) <= Year($(vEndDate))-1) And (Month(TempDate) <= Month($(vEndDate))) Or Year(TempDate) = Year($(vEndDate))),'YTD','ROY') as YTD_Flag

// if((Date(TempDate) >= Yearstart(Today()) AND Date(TempDate) <= Today()) OR (Date(TempDate) >= YearStart(AddMonths(Today(),-12)) AND Date(TempDate) <= addmonths(Today(),-12)),'YTD','ROY') as YTD_Flag
// -YearToDate(TempDate, 0, 1) As YTD_Flag,
// -YearToDate(TempDate, -1, 1) As LYTD_Flag

;
Load
$(vStartDate)+RecNo()-1 As TempDate
AutoGenerate($(vNumDays));

Can anyone point me in the right direction here.  My plan is to auto calculate the last 12 months automatically each month.

[Anonymous]

Try:

if(Date(TempDate) >= YearStart(AddMonths(Today(),-12)) AND Date(TempDate) <= addmonths(Today())),'YTD','ROY') as YTD_Flag

[tobias_klett]

Hi Dermot,

use MONTH_YEAR as Dimension an create another numeric monthyear (e.g. 201412) and use it as first Expression. Then got to Dimension Limits and tick the "Restrict...values...first Expression." > "Show only largest 12".

Hope this helps

Tobias

[kiranmanoharrode]

Hi Dermot,

Your Master calender script is fine. if you want to calculate prev month's Qty, do follow below procedure.

Create below variables:

vMaxYear = Max(Year);

vMaxMonthNum =Max(MonthNum);

vPrevMonthYear =  if(vMaxMonthNum='12',vMaxYear-1,vMaxYear);

vPrevMonthNum = if(vMaxMonthNum='12',1,vMaxMonthNum-1);

Use below Expression:

=Sum( {<Year={"$(vPrevMonthYear)"},MonthNum={"$(vPrevMonthNum)"}>}Qty)

for Ex. selections are Year=2015 and Month=Jan , It will show value for DEC 2014

Regards,

Kiran