Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
How do you define vMaxSalesDate?
i defined
vMaxSalesDate= Max(Date)
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.
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
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?
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
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))
thanks a lot sunny
No problem my friend.
Best,
Sunny