Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

expressions for calculating previous month values

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.

3 Replies
sorrakis01
Specialist
Specialist

Hi,

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

Regards

Not applicable
Author

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
Specialist
Specialist

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