topic Re: Master Calendar with values present in dataset in QlikView

Master Calendar with values present in dataset

Mon, 19 Sep 2016 16:14:47 GMT

I have created the Master calendar with the help of the post from Josh Good.

I have the requirement to show year, month in list box for which the data present in the database. But I am getting the year in incremental fashion.

e.g. Suppose in my database, I have years 1900, 2003,2004,2005,2009,2014,2015,2016.

In the list box I am getting all the years starting from 1900 till 2016 like 1900,1901,1902 even though I don't have that data. How to address this issue?

Abhijit

Re: Master Calendar with values present in dataset

sinanozdemir — Mon, 19 Sep 2016 17:39:34 GMT

Hi,

That's the behavior of master calendar. You pretty much populate all the dates between the min and max date.

Thanks

Re: Master Calendar with values present in dataset

neelamsaroha157 — Mon, 19 Sep 2016 17:47:37 GMT

If you want dates only those present in your database then create a calendar from your date instead first populating all the dates between min & max date and then creating calendar based on those dates.

Load

YourdateField AS Date,

week(YourdateField ) As Week,

Year(YourdateField ) As Year,

Month(YourdateField ) As Month,

Day(YourdateField ) As Day,

YeartoDate(YourdateField )*-1 as CurYTDFlag,

YeartoDate(YourdateField ,-1)*-1 as LastYTDFlag,

inyear(YourdateField , Monthstart($(YourdateField )),-1) as RC12,

date(monthstart(YourdateField ), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(YourdateField ), Null()) as Quarter,

Week(weekstart(YourdateField )) & '-' & WeekYear(YourdateField ) as WeekYear,

WeekDay(YourdateField ) as WeekDay

Resident TableNameWhereDateIsPresent ;

hope this helps..

Re: Master Calendar with values present in dataset

Mon, 19 Sep 2016 17:56:40 GMT

Thank you Neelam,

This resolves my issue.

Abhijit

Re: Master Calendar with values present in dataset

neelamsaroha157 — Mon, 19 Sep 2016 18:24:00 GMT

No worries