Why does my monthly AUM instead turn into accumulated AUM?

[#1]

Dear Community,

The following is the front end text box that I wish to have display last month's AUM (when this current year is selected). However, right now it's currently displaying the entire year's AUM when 2014 is selected, and this is contrary to the programming, which says it should give only

AUM:

money(sum({$<[Marketing Qualified]=,PrimaryAdvisor.AdvisorFullName=,ReportingPeriodFlag={1}>}AccountValue.AccountValue))

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,

Anyone know why this is?

The full calendar script is below:

----------------------------

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;

[Anonymous]

Would something like this help ?

if ( MonthName(AddMonths(Today(),-12)) = MonthName(Date) ,1, 0 ) ) as ReportingPeriodFlag ,

I am not quite sure about the MaxAsOfDate you had in there in there though, so I may be misunderstanding something.

[#3]

Thanks! I think your if statement covers the past scenario. The reason for the double if statement on my flag is to cover both cases of the present and the past (the current year, 2014, and the past years).  It does very well at the latter; the former, it needs work on.  In the expression you were talking about with the MaxAsOfDate--that is Max(AccountValue.AsOfDate), essentially an end of most recent month date field.

So the expression can be rewritten as:

If(Year(Today())=Year(Date) And Num(Month(Today())-1)=Num(Month(Max(AccountValue.AsOfDate))),1, (present)------why does this accumulate? or, why does this accumulate when in the text box?

If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag, (past)

Thanks for your help!

[#4]

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

On above expression, why you add this piece of code  Num(Month(Today())-1)=Num(Month(MaxAsOfDate)) ?

You are compareing two constant values Today() always one value and MaxAsOfDate is only one unique value on the Tempcalendar table. If MaxAsOfDate is in previous month, all the current year values got 1.

[#5]

If I understand correctly, please use the below expression:

If(Year(Today())=Year(Date) And Num(Month(Date)-1)=Num(Month(MaxAsOfDate)),1,

If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag

[#6]

Dathu,

The reason for the Today() function is so that we have an expression like Current Year = Current Year Selected And Month - 1 = Max(Month) - 1.

The essence of this meant that if I select 2014 and it's 2014, and it's July, it should pick up June (the last month completed).

Unfortunately, if I use

If(Year(Today())=Year(Date) And Num(Month(Date)-1)=Num(Month(MaxAsOfDate)),1,

If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag

and it's the current year, the AUM is 0.

Thanks,

Ben

[#7]

My expression will do the same thing, I am creating the flag as 1 on the DATE field having all the June Month rows.

[#8]

All right--the year(date) was null and causing the problem of AUM = 0. However, I put FilteredYear in place of year(date).  Now, however, I've still got the same problem of accumulation over the year's months.  What's the reason?

Ben

If(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null()) as FilteredYear,
If(Year(Today())=(If(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null())) 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,