Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts
I have a master calendar and target table
my targets table having a same field month
In my master calendar starts from January 2015 to till date
but my targets table is having monthly targets for entire 2015
both month fields are in same format but in dash board its showing two different month field
Please Suggest
Let varMinDate = NUM(MAKEDATE($(vReviewYear),1,1));
Let varMaxDate = NUM(FLOOR($(vToday)-1));
//Let varMaxDate = NUM($(vToday));
//Let varMaxDate = NUM(MonthEnd($(vToday)));
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);
//Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
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 ,
Year(TempDate) As DEC_Year,
Month(TempDate) As DEC_Month,
Num(Month(TempDate)) As DEC_Month_Num,
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,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,
WeekDay(TempDate) as DEC_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Margin:
LOAD Month(Date#(trim(Month),'MMM')) as DEC_Month,
[Sales per day],
[Margin per day]
FROM
Count.xlsx
(ooxml, embedded labels, table is Sheet2) ;
May be force them to be the same even though they look the same:
LOAD MonthName(Date) as MonthYear
Hi John,
Will that be ok to upload your sample app. Presuming it could be a formatting issue from your target table.
V.
As always, more detail and a sample would help. But taking a wild guess - at least one of the two contains a time value, or you have formatted the m as months (eg MM-YYYY), but they are different dates. In other words they have the same text representation but different underlying values.
are the field name same? perhaps you do not have a link between the fields?
Hi Sunny,
Thanks.
now the months got joined but other fields in the target table are not properly mapped to the corresponding months.
I did populated the MONTH from mastercalendar and the targets in table box. screenshot below
Please help me on this
Can you share the script to get a better understanding of what is going on.
HTH
Best,
Sunny
HI
Please Find the Sample Qvw and data Attached
Please Suggest.
Margin:
LOAD Date#(Month,'MMM') as DEC_Month,
[Sales per day],
[Margin per day]
FROM
Count.xlsx
(ooxml, embedded labels, table is Sheet2);
HI Sasidhar
when i use Your above Script
I am getting the following Result