expressions for calculating previous month values

[#1]

Hi experts,

         I have a field name called Sales amount, branch names from a qvd.

         I need to calculate the total sales amount for all the months starting from 2014 to till date.

        i.e., if i calculate the total sales amount for feb 2015(for different branches), then it is the new field in a pivot table named as                        previous month sale & if i calculate the total sales amount for march 2015(for different branches), then it is the new field in a pivot            table named as current month sale.

         Similarly for all the months from 2014, i should have the total sales amount in a new dashboard.

         i have created variables like,

LET vToday = NUM(Today()); 

LET vReviewYearNo = Year($(vToday));

LET vMaxMonth = NUM(Month(Today()))-1;

LET vReview1MonthNo = NUM(MonthNAME($(vToday)));

LET vReview2MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-1)));

LET vReview3MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-2)));

LET vReview4MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-3)));

LET vReview5MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-4)));

LET vReview6MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-5)));

LET vReview7MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-6)));

LET vReview8MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-7)));

LET vReview9MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-8)));

LET vReview10MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-9)));

LET vReview11MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-10)));

LET vReview12MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-11)));

LET vReview13MonthNo = NUM(MonthNAME(ADDMONTHS($(vToday),-12)));

LET vReview1MonthNo00 = TEXT(NUM($(vReview1MonthNo),'00'));

LET vReview2MonthNo00 = TEXT(NUM($(vReview2MonthNo),'00'));

LET vReview3MonthNo00 = TEXT(NUM($(vReview3MonthNo),'00'));

LET vReview4MonthNo00 = TEXT(NUM($(vReview4MonthNo),'00'));

LET vReview5MonthNo00 = TEXT(NUM($(vReview5MonthNo),'00'));

LET vReview6MonthNo00 = TEXT(NUM($(vReview6MonthNo),'00'));

LET vReview7MonthNo00 = TEXT(NUM($(vReview7MonthNo),'00'));

LET vReview8MonthNo00 = TEXT(NUM($(vReview8MonthNo),'00'));

LET vReview9MonthNo00 = TEXT(NUM($(vReview9MonthNo),'00'));

LET vReview10MonthNo00 = TEXT(NUM($(vReview10MonthNo),'00'));

LET vReview11MonthNo00 = TEXT(NUM($(vReview11MonthNo),'00'));

LET vReview12MonthNo00 = TEXT(NUM($(vReview12MonthNo),'00'));

LET vReview13MonthNo00 = TEXT(NUM($(vReview13MonthNo),'00'));

LET vReview1MonthYear = MonthName($(vToday));

LET vReview2MonthYear = MonthName(ADDMONTHS($(vToday),-1));

LET vReview3MonthYear = MonthName(ADDMONTHS($(vToday),-2));

LET vReview4MonthYear = MonthName(ADDMONTHS($(vToday),-3));

LET vReview5MonthYear = MonthName(ADDMONTHS($(vToday),-4));

LET vReview6MonthYear = MonthName(ADDMONTHS($(vToday),-5));

LET vReview7MonthYear = MonthName(ADDMONTHS($(vToday),-6));

LET vReview8MonthYear = MonthName(ADDMONTHS($(vToday),-7));

LET vReview9MonthYear = MonthName(ADDMONTHS($(vToday),-8));

LET vReview10MonthYear = MonthName(ADDMONTHS($(vToday),-9));

LET vReview11MonthYear = MonthName(ADDMONTHS($(vToday),-10));

LET vReview12MonthYear = MonthName(ADDMONTHS($(vToday),-11));

LET vReview13MonthYear = MonthName(ADDMONTHS($(vToday),-12));

LET vReview1YearNo = Year($(vToday));

LET vReview2YearNo = Year(ADDMONTHS($(vToday),-1));

LET vReview3YearNo = Year(ADDMONTHS($(vToday),-2));

LET vReview4YearNo = Year(ADDMONTHS($(vToday),-3));

LET vReview5YearNo = Year(ADDMONTHS($(vToday),-4));

LET vReview6YearNo = Year(ADDMONTHS($(vToday),-5));

LET vReview7YearNo = Year(ADDMONTHS($(vToday),-6));

LET vReview8YearNo = Year(ADDMONTHS($(vToday),-7));

LET vReview9YearNo = Year(ADDMONTHS($(vToday),-8));

LET vReview10YearNo = Year(ADDMONTHS($(vToday),-9));

LET vReview11YearNo = Year(ADDMONTHS($(vToday),-10));

LET vReview12YearNo = Year(ADDMONTHS($(vToday),-11));

LET vReview13YearNo = Year(ADDMONTHS($(vToday),-12));

i have tried using appropriate variables for the particular month i need. but its not working.

please guide me the way i should use in this case as soon as possible.

[sorrakis01]

Hi,

Maybe you can use a master calendar and create a PeriodID. And use Set Analysis to calculate it.

Regards

[#3]

i have the following master calendar

Let varMinDate = NUM(MAKEDATE($(vReviewPrevYear),1,1)); 

Let varMaxDate = NUM(FLOOR($(vToday)-1)); //NUM(FLOOR(MonthEnd($(vToday)-1))); 

     Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

     Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

     Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as DECDDT 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar:

Load *, DEC_WeekYear & '-' & NUM(DEC_WeekNumber,'00') as DEC_YearWeek;

Load *,

     Div( DECDDT - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,

     Year( DEC_WeekYearRefDate ) as DEC_WeekYear;

Load *,

     Date( YearStart( If( $(vCal_BW), DECDDT, DEC_WeekRefDate )) + $(vCal_RD) - 1) as DEC_WeekYearRefDate ;

Load *,

     Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as DEC_WeekRefDate ;

Load 

               DECDDT,

               NUM(DECDDT) AS CalendarNDate, 

               Date(DECDDT) AS CalendarDate,

               WeekStart(DECDDT, 0, $(vCal_FD)) as CalendarWeekStart ,

               Year(DECDDT) As CalendarYear, 

               Month(DECDDT) As CalendarMonth, 

               MonthName(DECDDT) As CalendarMonthYear, 

               Day(DECDDT) As DEC_Day, 

               If(InMonthToDate(DECDDT, '$(vToday)',0),1,0) as DEC_CurMTDFlag, 

               If(InMonthToDate(DECDDT, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as DEC_LastMTDFlag, 

               If(InMonthToDate(DECDDT, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as DEC_PrevMTDFlag, 

               YeartoDate(DECDDT)*-1 as DEC_CurYTDFlag, 

               YeartoDate(DECDDT,-1)*-1 as DEC_LastYTDFlag, 

               'XXXX' as DEC_CurLWFlag,

               IF(DECDDT<=$(varMaxDate),1,0) as DEC_TD_Flag, 

               WeekDay(DECDDT) as DEC_WeekDay        

Resident TempCalendar 

Order By DECDDT ASC; 

Drop Table TempCalendar; 

Drop Fields DEC_WeekRefDate, DEC_WeekYearRefDate;

     Set vCal_FD = ;

     Set vCal_BW = ;

     Set vCal_RD = ;

DECDDT is the date field in the sales table.

please help me with the expressions i need to go.

[simenkg]

Just create a chart with CalendarMonthYear as dimension and Sum([Sales Amount]) as the expression.