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

       

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