8 Replies Latest reply: Nov 10, 2010 6:10 PM by John Witherspoon RSS

    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!

       







       

        • Set Analysis and Prior Headcount
          susant Kumar swain

          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

              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

                  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

                       

                       



                        • Set Analysis and Prior Headcount

                          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


                            • Set Analysis and Prior Headcount
                              John Witherspoon

                              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

                                  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
                                      John Witherspoon

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