Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 sets of figures, the one is actual and the other is budget. The budget figures run up to the end of the year (ie Dec 2017). My actual figures is up to Jun 2017. These are all in one fact table. I then created a calendar linking to this fact table.
This is my current month formula, however it is giving me figures for Dec 17 instead of June 17
Sum({<Year = {'$(vMaxYear)'}, Month = {'$(vMaxMonth)>}Value)
Below is my script for the Calendar.
How do i change my expression in the front end so that it shows current month as June. Or do i change the Calendar script in the backend?
Kind regards
Nayan
MinMaxDates:
LOAD
MIN(FieldValue('GLDate', RecNo()))-0 AS MinDate,
// MonthEnd(AddMonths(Today()-1,-1)) as MaxDate, //May need to bring back in
yearend (Today()) as MaxDate,
MAX(FieldValue('GLDate', RecNo()))-0 AS MaxGLDate
AUTOGENERATE FieldValueCount('GLDate');
LET vMinD = FieldValue('MinDate', 1);
LET vMaxD = FieldValue('MaxDate', 1);
LET vMaxD2 = FieldValue('MaxGLDate', 1);
LET NumOfDays = vMaxD - vMinD + 1;
DROP TABLE MinMaxDates;
CalendarTemp:
LOAD $(vMinD) + rowno() - 1 AS GLDate
AUTOGENERATE NumOfDays;
Calendar:
LOAD DISTINCT
GLDate,
DUAL(DATE(GLDate), NUM(DATE(GLDate))) as Date,
day(GLDate) as Day,
Week(GLDate) as Week,
DUAL(month(GLDate), NUM(month(GLDate), '00')) as Month,
YEAR(GLDate) as Year,
WeekDay(GLDate) as Weekday,
Date(WeekEnd(GLDate)) as [Week Ending],
if(match(weekday(GLDate),'Sat','Sun'), 'Weekend', 'Weekday') as Weekend,
Text(Day(GLDate)) & ' ' & Text(Month(GLDate)) as DayOfMonth,
Date(MakeDate(Year(GLDate), Month(GLDate)),'MMM-YY') as YearMonth
RESIDENT CalendarTemp;
DROP TABLE CalendarTemp;
May be you missing an apostrophe here?
Sum({<Year = {'$(vMaxYear)'}, Month = {'$(vMaxMonth)'}>}Value)
In your text object try pulling these variables and see what values you are getting?
However, these days dev's are using the below Calendar script as a practice. May be you can try using this. Taken from Rob's Cookbook reference guide.
Calendar:
Load
Num(Floor(TempDate)) AS %Key_LoginDate,
'W' & week(TempDate) AS ANR_Week,
Year(TempDate) AS ANR_Year,
Month(TempDate) AS ANR_Month,
Day(TempDate) AS ANR_Day,
YeartoDate(TempDate)*-1 AS ANR_CurYTDFlag,
YeartoDate(TempDate,-1)*-1 AS ANR_LastYTDFlag,
//inyear(TempDate, Monthstart($(varMaxDate)),-1) AS RC12,
//date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
'Q' & ceil(month(TempDate) / 3) AS ANR_Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS ANR_WeekYear,
Date(TempDate, 'MMM-DD') AS ANR_MonthDay,
WeekDay(TempDate) AS ANR_WeekDay,
if(match(weekday(GLDate),'Sat','Sun'), 'Weekend', 'Weekday') as Weekend,
Text(Day(GLDate)) & ' ' & Text(Month(GLDate)) as DayOfMonth
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate,
maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('GLDate', recno()))-1 AS mindate,
max(FieldValue('GLDate', recno())) AS maxdate
AUTOGENERATE FieldValueCount('GLDate');
Add any additional fields accordingly.
Hi Vishwarath
Thank you for your reply. When i was typing the formula on this forum, i mistakenly left out the apostrophe. In my formula, i do have the formula. I have input box and text box, and the vMaxMonth still shows December 17.
Kind regards
Nayan
Hi Vishwarath
Sorry, I didn't scroll down on your reply. Will try it and let you know.
Kind regards
Nayan
Ok if you still facing any issue can you share a sample app here please?