2 Replies Latest reply on Aug 6, 2015 12:48 AM by Alan Toomey

    Daily average parameter driven calcualation at a monthly and quarterly level

    Alan Toomey

      Hi All,


      I am trying to get a report to show a daily average at a monthly or quarterly level. The fields are driven by parameters and an if statement to apply them per department.

      Its posing more of a challenge than I expected and all my searching on the forum hasnt nailed it for me yet.


      I have created a sample using Superstore, attached.

      The purpose of the report is for headcount analysis. Its designed for technical support which is why im using number of records (number of cases) for the sample.

      Im just treating the Category as if they were departments for this exerciser.


      I have Parameters to allow the user to update %growth, daily close average per person and training hours.

      The close average can be different per Category so an if statement is needed at the calculation level. This causes issues as any solution i try ends up in the no aggr field in an if statement trap.


      So the calculation breaks down into:

      Group Headcount

      IF [Category] = 'Furniture' THEN ([Cases + Growth] - [Training Offset]) / [Daily Close - Furniture]

      ELSEIF [Category] = 'Technology' THEN ([Cases + Growth] - [Training Offset])/ [Daily Close - Technology]

      ELSEIF [Category] = 'Office Supplies' THEN ([Cases + Growth] - [Training Offset])/ [Daily Close - Office Supplies]



      Cases + Growth

      [Number of Records] + [Growth Offset]


      Growth Offset

      [Number of Records] * [Growth - Case Arrival]


      Training Offset

      ([Cases + Growth] * ([Weekly Training Hours per Rep]/40))



      [Daily Close - Furniture]

      [Daily Close - Technology]

      [Daily Close - Office Supplies]

      [Weekly Training Hours per Rep]

      [Growth - Case Arrival]


      I can do all this daily and my current work around is to use reference lines to show the average across the month or quarter.

      But, ideally I would like to be able to give a monthly or quarterly summary level.


      Any help is greatly appreciated. Im thinking what i have may be as good as it gets.



        • 1. Re: Daily average parameter driven calcualation at a monthly and quarterly level

          Hi Alan,


          I just saw that your post went unanswered, I am surprised why it did because it is a well explained problem. Do you still need help with this? I just created something basic to give you an idea, you can of course modify it based on your needs or let me know if I was completely off the hook in interpreting this correctly. If you are looking for a date level summary, you are just one step away literally.


          You can create and additional string parameter that lists day, week, month, quarter and year. Create custom dates from the order date dimension by right clicking the order date > transform > create custom date. Do that for all date levels you need. Create a calculated field over this parameter such as:


          CASE [Date Part Selection]

          WHEN 'Quarter' THEN [Order Date (Quarters)]

          WHEN 'Month' THEN [Order Date (Month / Year)]

          WHEN 'Year' THEN [Order Date (Years)]

          WHEN 'Day' THEN [Order Date (Days)]



          Instead of having the actual order date on columns, place this calculated field on columns as exact date and discrete.


          You can now choose the date level at which you need your data to be displayed. Hope this helps.






          • 2. Re: Daily average parameter driven calcualation at a monthly and quarterly level
            Alan Toomey

            Thanks Pooja.


            The numbers are still off though.

            If you look at the calculation results when switching between daily, monthly etc, they differ greatly.

            This is because the targets set are at a daily level. Im looking for a way to continue to apply the figures to daily figures but view from a monthly or quarterly level.


            My work around for now is to use the trend average lines.