Skip to main content
Announcements
Happy New Year! Cheers to another year of collaboration, connections and success.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to display Current Month Figures

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;

4 Replies
vishsaggi
Champion III
Champion III

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.

Anonymous
Not applicable
Author

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.

vMaxMonth.JPG

Kind regards

Nayan

Anonymous
Not applicable
Author

Hi Vishwarath

Sorry, I didn't scroll down on your reply.  Will try it and let you know.

Kind regards

Nayan

vishsaggi
Champion III
Champion III

Ok if you still facing any issue can you share a sample app here please?