calendar

[#1]

Hi Experts,

This is my master calendar

Temp:

Load

               min(Date) as minDate,

               max(Date) as maxDate

Resident Sales;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

     Set vCal_FD = 3; // 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}

QuartersMap:

MAPPING LOAD 

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

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( Date - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,

          Year( DEC_WeekYearRefDate ) as DEC_WeekYear;

     Load *,

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

     Load *,

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

Load

               TempDate AS Date,

               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,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               RIGHT(Year(TempDate) & ' ' & ApplyMap('QuartersMap', month(TempDate), Null()), 2) AS CalendarQuarter,

               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;

i need to do a calculation like

SUM({<DEC_MonthYear =,DEC_Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Points)

where vMaxSalesDate=Max(Date)

when i select different month or different date, i m getting expected values. but when i select a quarter, i m getting all '0'.

how should i change my expression or anything wrong in my calendar?

Pls help...

[sunny_talwar]

I think you need to add all the calendar fields to your expression wherever you would possible be making selections:

SUM({<DEC_MonthYear =, Quarter = , CalendarQuarter =, DEC_Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Points)

[#3]

sunny

thanks for the reply.

still showing '0' only.

[sunny_talwar]

You are making selection in one of the two fields I added to the expression right?

[#5]

i did selected quarter q4 and values are '0'

[sunny_talwar]

I am not sure what might be causing it. Would you be able to share a sample?

[#7]

sunny,

i think the problem is here. (Monthstart)

SUM({<DEC_MonthYear =, Quarter = , CalendarQuarter =, DEC_Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Points)

if i replace it with Quarterstart, its working fine when i select quarter.

but my requirement is to give Monthtodate, quarter selection, date selection evering in single expression.

how can i get it. pls help

[tresesco]

Check if you are getting the value right in variable vMaxSalesDate

[#9]

yes, i m getting right value for the variable vMaxSalesDate

[#10]

somebody help pls