Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

Tags (3)
Community Browser