Month and Year Set Analysis Comparison

[#1]

Hi Everyone,

Can someone tell me why this expression is returning nothing. The dates are both in MMM-YYYY format. My dimension is CalendarMonthAndYear and the dashboard is Comings and Goings the object is Monthly Turnover.  I have attached the .qvw for someone if they want to look at it. Much appreciated.

(Count ({<CalendarMonthAndYear = {"<=$(=MonthEnd(Date(Date#(NLE_MonthName, 'MMM-YYYY'))))"}>} EmployeeDirectory.ID))

Thank you,

OnePuttGirl

[whiteline]

Hi.

It seems, everithing works well.

[#3]

whiteline,

Did you check my Monthly turnover object. It doesn't populate and my expression isn't working. If you look at the Yellow NLE Date Code on the right it is ignoring the Set Analysis. Do you have an suggestions?

Thank you,

OnePuttGirl

[GabrielAraya]

mmm I thought that you have problem with the expression ..

You Wrote:

(Count ({<CalendarMonthAndYear = {"$(=MonthEnd(Date(Date#(NLE_MonthName, 'MMM-YY'))))"}>} EmployeeDirectory.ID))

//>*<CalendarMonthAndYear = {">=$(=MonthEnd(Date(Date#(EmployeeDirectory.DOH, 'MMM-YYYY'))))"}

//(Count({<DOH_Year={'<=$(vCurrentYear)'}>} EmployeeDirectory.ID))

if (([CalendarMonthName])='Sep',(Count({<NLE_Year={'$(vCurrentYear)'},NLE_Month={'Sep'}>} EmployeeDirectory.ID))/(Count({<EmployeeDirectory.DOH={'<=$(09/30/2012)'},NLEIndicator={'0'}>} EmployeeDirectory.ID)))

//if (([CalendarMonthName])='Sep',(Count({<NLE_Year={'$(vCurrentYear)'},NLE_Month={'Sep'}>} EmployeeDirectory.ID))/(Count({<NLE_Year={'<=$(vCurrentYear)'},NLE_MonthNum={'<=9'}>} EmployeeDirectory.ID)))

//    if (WeekDay([vToday])='Tue',(Sum({$<DaysFilled.Date = {'$(=vSevenDayAgo)'} >} DaysFilled.Days)-Sum({$<DaysFilled.Date = {'$(=vEightDayAgo)'} >}DaysFilled.Days)),

//         if (WeekDay([vToday])='Wed',(Sum({$<DaysFilled.Date = {'$(=vEightDayAgo)'} >} DaysFilled.Days)-Sum({$<DaysFilled.Date = {'$(=vNineDayAgo)'} >}DaysFilled.Days)),

//             if (WeekDay([vToday])='Thu',(Sum({$<DaysFilled.Date = {'$(=vNineDayAgo)'} >} DaysFilled.Days)-Sum({$<DaysFilled.Date = {'$(=vTenDayAgo)'} >}DaysFilled.Days)),

//                 if (WeekDay([vToday])='Fri',(Sum({$<DaysFilled.Date = {'$(=vTenDayAgo)'} >} DaysFilled.Days)-Sum({$<DaysFilled.Date = {'$(=vElevenDayAgo)'} >}DaysFilled.Days)))))))

Fist of all, the field CalendarMonthAndYear has a format MMM YY and you wrote:

"$(=MonthEnd(Date(Date#(NLE_MonthName, 'MMM-YY'))))"

with format MMM-YY with dash ..

Second .. the DATE function is in wrong position .. It should be

"$(=Date(MonthEnd(Date#(NLE_MonthName, 'MMM YY')),'MMM YY'))"

another issue is ... you have

(Count ({<CalendarMonthAndYear = {"$(=MonthEnd(Date(Date#(NLE_MonthName, 'MMM-YY'))))"}>} EmployeeDirectory.ID)) and the next line no commented is an IF ... I thin that you omitted an operator ..

Gabriel

[#5]

Gabriel,

Isn't the CalendarMonthAndYear date format is 'MMM YYYY'  which would be 'Jan 2012 and doesn't 'MMM YY' return 'Jan 12'? I still am not getting any return results from the expression you gave me. The resulting count of 566 is the total records in the table and is ignoring the Set Analysis. Any other suggestions. Maybe you can attach your working expresssion.

Thank you,

OnePuttGirl

[GabrielAraya]

mm Test the following expression

Count ({<CalendarMonthAndYear = {"$(=Date(MonthEnd(Date#(NLE_MonthName, 'MMM YYYY')),'MMM YYYY'))"}>}

          EmployeeDirectory.ID)

[#7]

Gabriel,

I get the same thing returned. Is there anyway I can set the CalendarMonthAndYear and then test that the expression is returning the correct count?

[GabrielAraya]

mm .. I did some test ... if the final result is 566 ..

The idea is to get a table similar to the left side? right

Gabriel

[#9]

Yes, Correct what you have on the right is what I am looking for. Can you attach the .qvw that gave you this object? Thank you so much for helping me.

[GabrielAraya]

Here you are ...

Gabriel