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]

How do you define vMaxSalesDate?

[#3]

i defined

vMaxSalesDate= Max(Date)

[sunny_talwar]

Lets say you select quarter of Q2 (1st Apr 2015 - 30th Jun 2015)

You would like to see the result for >=1st June 2015<=30th June 2015, is that right?

Now put this in a text box object and see if you get that range in the format of your date field

='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate)

If you do, then try this:

Sum({<DEC_MonthYear =, Quarter = , CalendarQuarter =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>}Points)

If you don't, we will have to start fixing this first.

[#5]

sunny,

i have only 3 months data. i.e., sep 2015 to nov 2015

Quarters Q3 & Q4.

i tried this expression in a text box

='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate)

when i selected Q3, i m getting date range as 01/09/2015 to 30/09/2015

but when i select Q4, i m getting date range as 01/11/2015 to 18/11/2015 which is not correct.

i should get 01/10/2015 to 18/11/2015 for Q4 selection.

pls help me to correct this

[sunny_talwar]

Why would you get 01/10/2015? MonthStart for 18/11/2015 is 01/11/2015? What is your logic for seeing 01/10/2015?

[#7]

sunny,

my pivot table expressions are MTD calculations with the set analysis expressions

Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field)

But my client needs quarterly values too in the same table when they select the quarters Q3 or Q4.

is that possible with above expression?

if not, how should i change the above expression?

pls help me out

[sunny_talwar · Accepted solution]

So if quarter is selected you would like to see quarterly info and when its not you would like to see monthly info? Is that correct? If it is then, may be this:

If(GetSelectedCount(Quarter) = 1, Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(QuarterStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field), Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field))

[#9]

thanks a lot sunny

[sunny_talwar]

No problem my friend.

Best,

Sunny