Fiscal Year as Filter - how to add years without data

[#1]

Hi,

I'm currently implementing a calendar into my QV application. For that, I've created a master calendar containing a mapping between years and fiscal years. So far so good.

Now I would like to add a filter (listbox) for all years with existing data. Problem is, that I have data for the years 2013 - 2016 and 2018, but nothing for 2017. Nevertheless, I would like to add 2017 to the filter to avoid questions why this year is missing. How can I achieve that?

That's what I have:

List Box Properties -> Field -> <Expression>: =if(SERVICE_DATE > 0, Geschäftsjahr)

Thanks.

[#2]

Hi,

Can you try to add a new field which has a column years that can be used to create a listbox. As this column is included in the required table(Excel or DB), QV automatically maps the relevant data.

Thanks,

Sai

[nilesh_gangurde]

Hi,

Creating master calender with the dummy date can work here.

here is the syntax for that.

create calender and then link it to your data with date field.

• QuartersMap: 
• MAPPING LOAD  
• rowno() as Month, 
• 'Q' & Ceil (rowno()/3) as Quarter 
• AUTOGENERATE (12); 
•  
• Temp: 
• Load 
•                min(OrderDate) as minDate, 
•                max(OrderDate) as maxDate 
• Resident Orders; 
•  
• Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
• Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
• DROP Table Temp; 
•  
• TempCalendar: 
• LOAD 
•                $(varMinDate) + Iterno()-1 As Num, 
•                Date($(varMinDate) + IterNo() - 1) as TempDate 
•                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
•  
• MasterCalendar: 
• Load 
•                TempDate AS OrderDate, 
•                week(TempDate) As Week, 
•                Year(TempDate) As Year, 
•                Month(TempDate) As Month, 
•                Day(TempDate) As Day, 
•                YeartoDate(TempDate)*-1 as CurYTDFlag, 
•                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
•                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
•                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
•                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
•                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
•                WeekDay(TempDate) as WeekDay 
• Resident TempCalendar 
• Order By TempDate ASC; 
• Drop Table TempCalendar; 

-Nilesh