Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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)
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 |
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.
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 |
"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.