Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have added below M.Calendar and my problem is I can't find the column(MonthYear) in my pivot table as attached but I can see in list box. and I can see in Table viewer also.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Reporting_Date) as minDate,
max(Reporting_Date) as maxDate
Resident TradeExposure;
//EXIT Script
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
//Left Keep(TradeExposure)
Load
TempDate AS Reporting_Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
My guess is that there is no link between dimension fields (e.g. Division) and the Reporting_Date. But it is hard to tell without the app...
HIC
Hi
Your master calendar looks OK to me, so I suspect the problem is the expression in the pivot table. If the expression is "=MonthYear", then it is possible that there is more than one possible value in each row, so it cannot display a value. Try count or concat to verify this and modify the formula (with an aggregation of some type), so it can only return a single value for each row.
HTH
Jonathan
Hi HIC,
We have association as attached and not only MonthYear and all fields in M.Cal, I can't find in pivot table.
Only one date availabe in my data as below:
.
MonthEnd(Date#(Reporting_Date,'YYYYMM')) as Reporting_Date,
Hi HTH,
Its a dimension.
Hi,
you will use this script
Date(SubField(TempDate,' ',1),'MMM-YYYY') AS MonthYear, instead of
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
In above expression, you will try to get 1 or 2 or 3 instead of 1
Hi Krishnama,
Same issue exists with your script.
Hi,
Simply use
MonthName(TempDate) as MonthYear
take this field in listbox check did you get value or not??
Regards
Hi Max,
its working in the List box and not in the Pivot table
Hi,
Then create Straight Table take
MonthYear as dimesion and use any simple expression in it
check did you get any value against it or not??
also take Year in list box, Month in list box and check when you select any value from MonthYear list box the related value reflected in Month and Year List box..
If possible post your app.
Regards