topic Re: Month and Year Set Analysis Comparison in QlikView

Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 16:57:31 GMT

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

Re: Month and Year Set Analysis Comparison

whiteline — Thu, 04 Oct 2012 18:28:09 GMT

Hi.

It seems, everithing works well.

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 19:12:06 GMT

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

Re: Month and Year Set Analysis Comparison

GabrielAraya — Thu, 04 Oct 2012 19:35:20 GMT

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

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 19:48:12 GMT

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

Re: Month and Year Set Analysis Comparison

GabrielAraya — Thu, 04 Oct 2012 19:58:17 GMT

mm Test the following expression

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

EmployeeDirectory.ID)

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 20:27:42 GMT

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?

Re: Month and Year Set Analysis Comparison

GabrielAraya — Thu, 04 Oct 2012 20:41:51 GMT

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

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 20:51:28 GMT

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.

Re: Month and Year Set Analysis Comparison

GabrielAraya — Thu, 04 Oct 2012 20:57:36 GMT

Here you are ...

Gabriel

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 21:16:41 GMT

The object on the left is the count without Set Analysis. 566 is the total count of employee with out any date selections. What was the set analysis of the object on the right?

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 21:22:18 GMT

hellow!!

visit my post ---> thanks

http://community.qlik.com/thread/63138?tstart=0

Re: Month and Year Set Analysis Comparison

GabrielAraya — Thu, 04 Oct 2012 21:36:57 GMT

Hi .. but, really do you need set analysis ? If the object in the right side has the correct information .. yuo don't need SA .. perhaps I didn't understand your requeriment .. in general I use K.I.S.S ( Keep It Simple and Straightforward)

Re: Month and Year Set Analysis Comparison

Thu, 04 Oct 2012 21:45:40 GMT

I guess I haven't given you the total requirement. What I am looking for is Percentage of Turnover by Month. The complete set analysis would look something like this:

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

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

So I am taking the people in Sep. who are no longer here and then dividing it by the total amount of people who were here at that time. I am looking for a percentage of Turnover for that month. I had to build a independant calendar because I couldn't use the DOH_Date or the NLE_Date and that is why I am using CalendarMonthName from code I found on the Community. Any ideas I know you are probably growing weary.

Thank you,

OnePuttGril