Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis and Prior Headcount

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!







8 Replies
qliksus
Specialist II
Specialist II

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

Not applicable
Author

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.

Not applicable
Author

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)



Not applicable
Author



Not applicable
Author

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_OriginalJan-Mar 2010Apr-Jun 2010
MonthName(max(feeddate),-3)
//MonthName(max(feeddate,-3))
4014840238
max(feeddate)3/1/20106/1/2010
Prior Quarter-End DS10001000




Range_OriginalJan-Mar 2010
MonthName(max(feeddate),-3)
//MonthName(max(feeddate,-3))
40148
max(feeddate)3/1/2010
Prior Quarter-End DS894


johnw
Champion III
Champion III

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.

Not applicable
Author

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


johnw
Champion III
Champion III

"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.