topic Re: How to display Current Month Figures in QlikView

How to display Current Month Figures

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: How to display Current Month Figures

vishsaggi — Wed, 28 Jun 2017 19:45:29 GMT

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.

Re: How to display Current Month Figures

Anonymous — Wed, 28 Jun 2017 19:51:32 GMT

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

Re: How to display Current Month Figures

Anonymous — Wed, 28 Jun 2017 20:12:18 GMT

Hi Vishwarath

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

Kind regards

Nayan

Re: How to display Current Month Figures

vishsaggi — Wed, 28 Jun 2017 20:28:32 GMT

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