Dear Community,
I'm trying to create a flag that has two parts. It will go inside a listbox and be manipulated by year, month, and quarter fields. However, when one presses the current year, the flag will essentially choose the current month (by way of AccountValue.AsOfDate), and for all previous years, it will choose the end of year, December. The second part is working well, but the first part of the expression is more complicated because it requires a maximum function, I think. However, when I put the maximum function on the expression, we have the problem that it accumulates the current year's months if a specific month is not chosen. I'd like instead for the listbox to give a "passive" sales number on the dashboard, equal to the current month's sales number when clicked. The 2013-past sales numbers are all correct.
Can I accomplish a flag (or solution) where I can:
1) Do the above and still allow for other months to be clicked (and not 0?) I notice that's a problem if you code too stringently with flags. The same would go for the accumulation of months in the present year, if at all possible. (It'd be nice to know how much we have total in sales by clicking and dragging, say, Jan-Mar.)
--Ben
Thanks for your help! Calendar code is below, including flag code in bold.
******For purposes of expression only: Max(AccountValue.AsOfDate)=MaxAsOfDate in script************************
If(Year(Today())=Year(Date) And Num(Month(Today())-1)=Num(Month(Max(AccountValue.AsOfDate))),1,
If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,
----------------------------
MAPPING LOAD
Num(CalendarExclusions.Date) as CalendarExclusions.Date,
CalendarExclusions.WorkingDayCounter
FROM C:\QlikView\Development\QVD\Historical Analysis\SalesQVDLoad\CalendarExclusions.qvd (qvd);
Set vDateSourceTable = 'LinkTable';
Let vToday = num(Today());
TempCalendar:
Load Distinct
Date(Daystart(Key|Date),'M/DD/YYYY') AS Date
Resident $(vDateSourceTable);
TempAccountValue:
outer join (TempCalendar)
Load
Max(AccountValue.AsOfDate) as MaxAsOfDate
Resident AccountValue;
Calendar:
LOAD Distinct
Date(Daystart(Date),'M/DD/YYYY') AS Key|Date,
Date,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),0) as WorkingDayFlag,
// IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), Date(Daystart(Date),'M/DD/YYYY')),Null()) as WorkingDayDate,
// Basic Date Dimensions
Year(Date) AS Year,
Year(Date) - 1 AS LYear,
Month(Date) AS Month,
Num(Month(Date)) AS MonthNumber,
// If(Year(Today())=Year(Date) And Num(Month(Today()))= $(vMaxAsOfDate),1,
If(Year(Today())=Year(Date) And Num(Month(Today())-1)=Num(Month(MaxAsOfDate)),1,
If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,
IF(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null()) as FilteredYear,
Week(Date) as WeekNumber,
Text(Date(monthstart(Date), 'MMM-YYYY')) AS MonthYear,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),Null()) * week(Date) as Week,
// Text(Date(monthstart(Date), 'YYYY')) & '-' & week(Date) AS YearWeek,
If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) AS Quarter,
If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) & '-' & Year(Date) AS QuarterYear,
Ceil(Day(Date)/7,1) as MonthWeekNumber,
.
.
.
.
TempCalendar;
Drop Table TempCalendar;
