topic Re: expressions for calculating previous month values in QlikView

expressions for calculating previous month values

Thu, 12 Mar 2015 09:15:30 GMT

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.

Re: expressions for calculating previous month values

sorrakis01 — Thu, 12 Mar 2015 09:19:30 GMT

Hi,

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

Regards

Re: expressions for calculating previous month values

Thu, 12 Mar 2015 09:30:21 GMT

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.

Re: expressions for calculating previous month values

simenkg — Thu, 12 Mar 2015 09:32:12 GMT

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