Master Calendar with values present in dataset

[#1]

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

[sinanozdemir]

Hi,

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

Thanks

[neelamsaroha157 · Accepted solution]

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..

[#4]

Thank you Neelam,

This resolves my issue.

Abhijit

[neelamsaroha157]

No worries