topic Re: Quarter Trend in QlikView

Quarter Trend

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

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.

Re: Quarter Trend

sasikanth — Mon, 22 Jan 2018 10:15:55 GMT

HI,

Two ways you can restrict

1) Use " LEFT KEEP " between your table and master Calendar

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

here Max(Year) and Max(MonthID) must be from your table not from Master calendar

Re: Quarter Trend

ajsjoshua — Mon, 22 Jan 2018 10:22:57 GMT

Dear Sasi,

I tried with left keep but i am getting the same result.

Re: Quarter Trend

sasikanth — Mon, 22 Jan 2018 10:31:38 GMT

order should be

Main table

LEFT KEEP

Master calendar

If not , please share your script screenshot

Re: Quarter Trend

agigliotti — Mon, 22 Jan 2018 10:45:08 GMT

what's your expected result for the bar chart and what are your filter selections ?

Re: Quarter Trend

ajsjoshua — Mon, 22 Jan 2018 10:56:11 GMT

Hi Andrea,

i have only 2017 data and i didnt filter anything.

My dimension should be YearQuarter

2017-Q1

2017-Q2

2017-Q3

2017-Q4

Re: Quarter Trend

sasikanth — Mon, 22 Jan 2018 11:01:01 GMT

What is the Link field between Master calendar and main table ? if possible please share your application it would be easy

Re: Quarter Trend

ajsjoshua — Mon, 22 Jan 2018 11:12:50 GMT

hi Sasi,

Date field is the link field and i am not allowed to show the script.i will create sample and update it.

Re: Quarter Trend

agigliotti — Mon, 22 Jan 2018 11:19:20 GMT

as sasikanth_narne‌ said you should provide a sample app with mock data to help you further.

Re: Quarter Trend

sunny_talwar — Mon, 22 Jan 2018 11:26:19 GMT

What script do you use to create YearQuarter field in the script?

Re: Quarter Trend

ajsjoshua — Mon, 22 Jan 2018 11:35:51 GMT

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;

Re: Quarter Trend

ajsjoshua — Mon, 22 Jan 2018 11:43:15 GMT

Dear All,

Please find the sample app in attachment !

Re: Quarter Trend

sasikanth — Mon, 22 Jan 2018 14:19:19 GMT

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

Re: Quarter Trend

ajsjoshua — Tue, 23 Jan 2018 11:26:25 GMT

Dear Sasi,

I am not using any financial time filters.

My starting month should be jan to dec .

Re: Quarter Trend

sasikanth — Tue, 23 Jan 2018 12:55:20 GMT

Please check your Quarter calculation once

What do you want to achieve through above exp?