topic Set Analysis and Prior Headcount in QlikView

Set Analysis and Prior Headcount

Tue, 12 Oct 2010 03:24:40 GMT

Hello...I'm trying to get the previous quarter's total headcount (sum of 3 months) using this:

Count ({1<Range_Original={$(=QuarterName(MAX(feeddate),-1))},Description={'Constant','Hire','Other Adds','Transfer In'},[Employee_Non-Employee]={'Employee'}>} Current_Count)

This results in a 0. feeddate is assigned to every month of data loaded - 1/1/2010, 2/1/2010, etc...

If I hard code the value for Range_Original like this, I'm able to get to the previous quarter's headcount:

Count ({1<Range_Original={'Apr-Jun 2010'},Region=P({$}Region),Description={'Constant','Hire','Other Adds','Transfer In'},[Employee_Non-Employee]={'Employee'}>} Current_Count)

I can't figure out where the syntax error is. I tried using double quotes like this, but this results in the other columns in my report getting zeroed out:

Range_Original={"$(=QuarterName(MAX(feeddate),-1))"}

Any help is greatly appreciated. Thanks!

Set Analysis and Prior Headcount

qliksus — Tue, 12 Oct 2010 06:27:19 GMT

Hi ,

I think the problem is becasue of not expanding the value try to use Range_Original={$(#=QuarterName(MAX(feeddate),-1))}

If it's still not working remove the label for this expression and try to find what value is returned from this function accordingly

we can decide

Set Analysis and Prior Headcount

Tue, 12 Oct 2010 14:17:15 GMT

I tried your syntax above, but I'm still getting zero. When I display QuarterName(MAX(feeddate),-1) in a column, I get the correct prior quarter value. For example, 'Apr-Jun 2010'. Any other ideas? Thanks.

Set Analysis and Prior Headcount

Thu, 14 Oct 2010 19:18:31 GMT

Hi...I still can't get this to work? Can anyone tell me what the syntax error is in my Set Analysis? My expression now looks like this:

Count ({1<Range_Original={"$(=QuarterName(Max(feeddate),-1))"},Description={'Constant','Hire','Other Adds','Transfer In'},[Employee_Non-Employee]={'Employee'}>} Current_Count)

Set Analysis and Prior Headcount

Thu, 14 Oct 2010 20:30:01 GMT

Set Analysis and Prior Headcount

Wed, 10 Nov 2010 17:33:32 GMT

Hello...I'm still struggling with this one. For "Prior Quarter-End Direct Staff" I've created this expression:

Count ({1<Range_Original={'$(=MonthName(max(feeddate),-3))'},Description={'Constant','Hire','Other Adds','Transfer In'},[Employee_Non-Employee]={'Employee'}>} Total Current_Count)

If I select a single quarter, everything looks good (894).

However, once I select multiple quarters, Prior Quarter-End Direct Staff is no longer correct. It looks like it is taking the max feeddate across all quarters and then doing the calculation. I need the max feeddate for the last month of the previous quarter. So in the table below, I would need Dec 2009 and Mar 2010. Any help is greatly appreciated. Thanks in advance.

Range_Original	Jan-Mar 2010	Apr-Jun 2010
MonthName(max(feeddate),-3) //MonthName(max(feeddate,-3))	40148	40238
max(feeddate)	3/1/2010	6/1/2010
Prior Quarter-End DS	1000	1000

Range_Original	Jan-Mar 2010
MonthName(max(feeddate),-3) //MonthName(max(feeddate,-3))	40148
max(feeddate)	3/1/2010
Prior Quarter-End DS	894

Set Analysis and Prior Headcount

johnw — Wed, 10 Nov 2010 22:12:05 GMT

A set is only calculated once for the entire chart, not once per row of the chart. So yes, it is taking the max feeddate across all quarters. You can't do what you want entirely with set analysis.

One solution is to generate an AsOf table that directly connects this quarter with last quarter. Something like this:

AsOfQuarter, QuarterType, Quarter
Jan-Mar 2010, Prior, Oct-Dec 2009
Jan-Mar 2010, Current, Jan-Mar 2010
Apr-Jun 2010, Prior, Jan-Mar 2010
Apr-Jun 2010, Current, Apr-Jun 2010
etc.

Use AsOfQuarter instead of Quarter as your dimension.

count({1<QuarterType={'Prior'},Description={'Constant','Hire','Other Adds','Transfer In'},[Employee_Non-Employee]={'Employee'}>} total Current_Count)

I'm sure I have some details wrong since I don't know your data. But hopefully the idea is clear.

Set Analysis and Prior Headcount

Wed, 10 Nov 2010 22:47:05 GMT

Thanks John. Does 'Quarter' need to exist prior to creating this AsOf table? I have a field called Range_Original which is used for data selections. I'm not really sure where I would put your code and when I create this 'AsOf' table, am I supposed to do a LOAD INLINE?

Range_Original:

Dec 2008

Jan 2009

Feb 2009

Mar 2009

Apr 2009

May 2009

Jun 2009

Jul 2009

Aug 2009

Sep 2009

Oct 2009

Nov 2009

Dec 2009

Jan 2010

Feb 2010

Mar 2010

Apr 2010

May 2010

Jun 2010

Jul 2010

Aug 2010

Sep 2010

Oct 2010

Oct-Dec 2008

Jan-Mar 2009

Apr-Jun 2009

Jul-Sep 2009

Oct-Dec 2009

Jan-Mar 2010

Apr-Jun 2010

Jul-Sep 2010

Oct-Dec 2010

Set Analysis and Prior Headcount

johnw — Wed, 10 Nov 2010 23:10:29 GMT

"Quarter" was my shorthand for "whatever field you use to store the quarter". It appears that you're storing the quarter in the Range_Original field. So you'd link it to Range_Original instead. No, you wouldn't do a load inline. The relationships here are always the same, and thus something that can be easily generated by computer. In other words, code it in script. How to do that depends on how your fields are defined. But let's say the Range_Original field has an underlying numeric value equivalent to the first day of the range, and that you have a "Quarter?" flag to identify when the range is a quarter. You might do something like this:

AsOf:
LOAD
Range_Original as AsOfQuarter
,'Current' as QuarterType
,Range_Original
;
LOAD DISTINCT Range_Original
RESIDENT SomeTable
WHERE "Quarter?" = 'Y'
;
CONCATENATE (AsOf)
LOAD
AsOfQuarter
,'Prior' as QuarterType
,addmonths(Range_Original,-3) as Range_Original
RESIDENT AsOf
;

Since how you do it is very sensitive to what your data looks like, this is really only an example. You won't be able to cut and paste the code into your script. But the idea is that you generate the table from the data you have. You turn it into a data modeling problem instead of a chart expression problem.