topic Re: previous year comparison in QlikView

previous year comparison

Mon, 22 Jun 2015 09:23:34 GMT

Hi Experts,

I have a bar chart showing recent 13 weeks' sales value.

my dimension is Yearweek.

it is showing the current week 2015-25 till the past 12 weeks.

now, i need to show the comparison of the same 13 weeks for the last year sales in the chart.

how can i do this.

pls help....

Re: previous year comparison

swuehl — Mon, 22 Jun 2015 09:44:21 GMT

You can change the dimension to a Week dimension, then create two expressions, one for this year, one for previous year.

To help you more, especially with some dimension sorting issues that can arise, please post a small sample QVW.

Re: previous year comparison

giakoum — Mon, 22 Jun 2015 09:57:13 GMT

Calculating rolling n-period totals, averages or other aggregations

Re: previous year comparison

Mon, 22 Jun 2015 13:56:46 GMT

Hi swuehl,

I have done the dimension as week and the expressions for current year and previous year.

but i have to make chart for the recent 13 weeks. i.e., current week is 25. so week 25 to....week 13(this year and previous year)

but now when i give the dimension limits to first 13 and sorting descending, i m getting week 52,51,50.....which is not the requirement.

attached the chart for reference.

pls help....

Re: previous year comparison

Mon, 22 Jun 2015 14:36:24 GMT

any other suggestions plssssss

Re: previous year comparison

swuehl — Mon, 22 Jun 2015 14:45:50 GMT

Maybe like attached as a starting point.

I am still thinking about how to solve the sorting when you cross year bounderies looking 12 weeks back in time (i.e. jumping in week number).

Re: previous year comparison

Wed, 24 Jun 2015 11:56:13 GMT

Hi Swuehl,

thanks for the response.

but my weekstart is every saturday.

My calendar is set as

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

WeekStart(TempDate, 0, $(vCal_FD) ) as WeekStart

and my expression is

sum({<Week = {">=$(=Weekstart(today(),-12, $(vCal_FD))<=$(=weekstart(today(),0, $(vCal_FD)))"}>}Amount)

but i m not getting the values

Pls help me on this

how should i use my expression

Re: previous year comparison

swuehl — Wed, 24 Jun 2015 12:07:02 GMT

I think you need to use field WeekStart in your set modifier:

sum({<WeekStart = {">=$(=Weekstart(today(),-12, $(vCal_FD))<=$(=weekstart(today(),0, $(vCal_FD)))"}>}Amount)

Re: previous year comparison

Wed, 24 Jun 2015 12:26:48 GMT

now the expression looks fine.

but dimension...

i have taken week

but its having 52 weeks

how this is possible and i have done only for 2015.

it is showing sales value as well for these 52 weeks which is not correct.

pls help me to limit the dimension.

My calendar

TempDate AS Date,

NUM(TempDate) AS NumDate,

WeekStart(TempDate, 0, $(vCal_FD) ) as WeekStart ,

Week(TempDate) as Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

MonthName(TempDate) As MonthYear,

Re: previous year comparison

Anonymous — Wed, 24 Jun 2015 12:32:18 GMT

I will suggest use week no. as separate column in script which will just contain 1,2,3,4,5,etc

the in your set expression use current week and current year for current year expression and

same expression for previous year but use year = currentyear -1

Thanks

BKC

Re: previous year comparison

swuehl — Wed, 24 Jun 2015 12:35:44 GMT

PRRAJENDRAN, I think your week numbering is not considering your change in week start yet.

What is the definition of week numbering in your calendar definition?

Please have a look into:

Redefining the Week Start

Redefining the Week Numbers

Regardless how your Week field is defined, you should see a limited range in your chart when you apply the set expression to all your expression in the chart, not all numbers between 1 and 52.

Re: previous year comparison

Wed, 24 Jun 2015 13:04:18 GMT

Swuehl,

this is my calendar.

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

Let varMaxDate = NUM($(vToday)-1);

//Let varMaxDate = NUM(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 TempDate

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

MasterCalendar:

Load *, WeekYear & '-' & NUM(WeekNumber,'00') as YearWeek;

Load *,

Div( DECDDT - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

Year( WeekYearRefDate ) as WeekYear;

Load *,

Date( YearStart( If( $(vCal_BW), DECDDT, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;

Load *,

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

Load

TempDate AS DECDDT,

TempDate AS Date,

NUM(TempDate) AS NumDate,

WeekStart(TempDate, 0, $(vCal_FD) ) as WeekStart ,

Week(TempDate) as Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

MonthName(TempDate) As MonthYear,

IF(TempDate<Today(),1,0) as TD,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as CurMTDFlag,

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

If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as LastMTDFlag,

If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as PrevMTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

can u pls hep me out now.

i m not getting how to define my expression

Re: previous year comparison

Wed, 24 Jun 2015 13:22:34 GMT

hi,

i m using like this

current year = sum({<DEC_WeekStart = {">= $(=Weekstart(today(),-12, $(vCal_FD)))<=$(=Weekend(today(),0, $(vCal_FD)))"}>}Amount)

Previous year= Sum({<DEC_WeekStart = {">=$(=Weekstart(addyears(today(),-1),-12, $(vCal_FD)))<=$(=Weekstart(addyears(today(),-1),0, $(vCal_FD)))"}>} Amount)

but still i m not getting correct weeks

Re: previous year comparison

swuehl — Wed, 24 Jun 2015 13:34:17 GMT

Can't see DEC_WeekStart in your calendar posted above.

Please post a sample QVW application that demonstrates your issue (you can modify the QVW I posted, using your calendar).

Re: previous year comparison

Wed, 24 Jun 2015 13:38:06 GMT

sorry sent wrong calendar.

this s my actual calendar

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

Let varMaxDate = NUM($(vToday)-1);

//Let varMaxDate = NUM(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 TempDate

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

TempDate AS DECDDT,

TempDate AS DEC_Date,

NUM(TempDate) AS DEC_NumDate,

WeekStart(TempDate, 0, $(vCal_FD) ) as DEC_WeekStart ,

Week(TempDate) as DEC_Week,

Year(TempDate) As DEC_Year,

Month(TempDate) As DEC_Month,

MonthName(TempDate) As DEC_MonthYear,

IF(TempDate<Today(),1,0) as DEC_TD,

Day(TempDate) As DEC_Day,

YeartoDate(TempDate)*-1 as DEC_CurYTDFlag,

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

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

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

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

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

inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,

WeekDay(TempDate) as DEC_WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: previous year comparison

swuehl — Wed, 24 Jun 2015 13:56:09 GMT

See attached your calendar integrated in my previous example.

Re: previous year comparison

sasiparupudi1 — Wed, 24 Jun 2015 14:02:19 GMT

Try Hardcoding value instead of the $(vCal_FD) in the Weekstart/end functions and see what you get?

Re: previous year comparison

Wed, 24 Jun 2015 14:28:32 GMT

Swuehl,

Thanks for the sample.

now the expression is fine.

but the dimension is showing 52 weeks.

i need only the current 13 weeks, i.e., week 13 to week 26.

how can i do this.

pls help...

screenshot attached

Re: previous year comparison

swuehl — Wed, 24 Jun 2015 14:35:58 GMT

Well, I think I've uploaded a working example above.

If you modified the example or tried to integrate the example in your app and this doesn't work in your setting,

you need

a) thoroughly compare your QVW with the example above

b) then describe what the differences are

c) or upload a working sample file

From just looking at the bitmap, I can't tell anything.

Re: previous year comparison

Thu, 25 Jun 2015 13:54:21 GMT

thanks a lot Swuehl

its working fine