previous year comparison

[#1]

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

[swuehl]

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.

[#3]

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

[#4]

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

[swuehl]

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).

[#6]

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; 

[swuehl · Accepted solution]

See attached your calendar integrated in my previous example.

[sasiparupudi1]

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

[#9]

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

[swuehl]

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.

[#11]

thanks a lot Swuehl

its working fine