Quarter Trend

[ajsjoshua]

Dear all,

I have only data for the year 2017 and i am using master calendar,but i am getting 2018 quarter in bar chart.

My dimension is YearQuarter

My expression is sum({$<Year = {$(=max(Year))},MonthID={"<=$(=Max(MonthID))"},Month= >} Amount)

PFA for screenshot

Regards,

Joshua.

[ajsjoshua]

Dear Sunny,

I am using mastercalendar

//**************Mastercalendar*************//

Calendar:

MinMax:

LOAD

    MIN(Date) AS MinDate,

    MAX(Date) AS MaxDate

RESIDENT patient ;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

    DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

    $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

Calendar:

LOAD

TempDate AS Date,

//if(Week(TempDate) = 53,'1',

//If(Year(TempDate) = '2016',Week(TempDate)+1,Week(TempDate) ))    as Week,

Week(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS Month,

DAY(TempDate) AS Day,

WEEKDAY(TempDate) AS WeekDay,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],

AutoNumber(YEAR(TempDate) & Week(TempDate), 'WeekID') as [WeekID],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2)))  AS Quarter,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS  MonthYear,

WEEK(TempDate) & '.' & YEAR(TempDate) AS WeekYear,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[ajsjoshua]

Dear All,

Please find the sample app in attachment !

[sasikanth]

HI Joshua,

Are you trying to create a Financial time filters ? then your chart showing correct values

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2  AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

use below script to generate Financial Time filters

Let vFM_NO=8;    // Starting Month of Fincial Year

Master_Calender:
Load
TempDate AS DATE_KEY,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day (TempDate) as Day,
Year(TempDate) + If(Month(TempDate)>=$(vFM_NO), 1, 0) as FinYear,
Mod(Month(TempDate)-$(vFM_NO), 12)+1        as FinMonth,
'Q'&ceil((Mod(Month(TempDate)-$(vFM_NO), 12)+1)/3) as FY_Qrter,

Dual((Year(TempDate) + If(Month(TempDate)>=$(vFM_NO), 1, 0))-1 &'-'& (Year(TempDate) + If(Month(TempDate)>=$(vFM_NO), 1, 0)),
(Year(TempDate) + If(Month(TempDate)>=$(vFM_NO), 1, 0))) as FY_Year,
dual(Month(TempDate),Mod(Month(TempDate)-$(vFM_NO), 12)+1)        as FY_Month
Resident  Temp_Calendar;

If you select FY Year 2016 then CY should be 2015-AUG to 2016-JUL as per your expression

[ajsjoshua]

Dear Sasi,

I am not using any financial time filters.

My starting month should be jan to dec .

[sasikanth]

Please check your Quarter calculation once

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2  AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

What do you want to achieve through above exp?