topic Set Analysis for Monthstart in QlikView

Set Analysis for Monthstart

Thu, 13 Oct 2011 16:34:37 GMT

I am new to Qlikview and would like some assistance with a set analysis syntax. I am looking for a statement that sums a DaysFilled.Days where the DaysFilled.Date is equal to the first day of data in the table for the month specified. So DaysFilled.Date may not have data for the first day of the month because it wasn't a working day, but it will have data for the first actual day of the month. My expression looks like this at the moment:

Sum ({<DaysFilled.Date={"$(=monthstart(DaysFilled.MonthDate))"}>}DaysFilled.Days)

Any and all feedback is welcomed.

Thank you in advance!

Re: Set Analysis for Monthstart

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 17:23:29 GMT

Hi,

Try this expression instead:

Sum({< DaysFilled.Date = {'$(=LastWorkDate(MonthStart(Today()), 1))'} >} DaysFilled.Days)

LastWorkDate() above should return the closer date to the first day of the month (october) to get 1 work day, so in this case it should return '03/10/2011' which is correct.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Set Analysis for Monthstart

Thu, 13 Oct 2011 17:36:52 GMT

The expression you gave me doesn't find any DaysFilled.Days. DaysFilled.Days are assignments and the days are already calculated in the table from the begin and end date of assignment. So for everyday of a working day there is a whole new set of data showing days filled into the future. I want the trend from the 1st working day of the month of DaysFilled.Date of the DaysFilled.days on that day. I hope that makes since.

Maybe you could explain the expression you wrote to me.

Thank you!

Re: Set Analysis for Monthstart

Thu, 13 Oct 2011 17:44:39 GMT

Also Miguel in the expression you found I didn't see where to close off the < and I'm so new that I don't know where it would go?

Thank you!

Re: Set Analysis for Monthstart

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 17:52:00 GMT

Hi, I've just checked that, and now the expression syntax is correct.

Obviously, that will only show data for October 3, 2011 because I'm using Today() function with LastWorkDate() and the closer date to get one work day starting on "01/10/2011" (month start for today's month) is "03/10/2011" (first working day of the month). You can set any other date field instead of Today(). It's worth noting that set analysis is evaluated once and for the whole chart, meaning that if your dimensions have different days for different months, the function within the set analysis will be evaluated just once and for all the possible values in the chart and may return unexpected results.

You may need then an If() conditional instead of a set analysis, but it would be easier if you post some sample data and what is the expected result, I'm afraid I'm not getting it.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica