0 Replies Latest reply: Jul 18, 2014 1:33 PM by Ben Paulson RSS

    Max Month versus Max Accumulated Current Year

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




      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,









           Num(CalendarExclusions.Date) as CalendarExclusions.Date,






      FROM C:\QlikView\Development\QVD\Historical Analysis\SalesQVDLoad\CalendarExclusions.qvd (qvd);






      Set vDateSourceTable = 'LinkTable';


      Let vToday = num(Today());






      Load Distinct


      Date(Daystart(Key|Date),'M/DD/YYYY') AS Date


      Resident $(vDateSourceTable);






      outer join (TempCalendar)




      Max(AccountValue.AsOfDate) as MaxAsOfDate


      Resident AccountValue;






      LOAD Distinct


      Date(Daystart(Date),'M/DD/YYYY') AS Key|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,










      Drop Table TempCalendar;