4 Replies Latest reply on Sep 28, 2016 7:35 AM by Benjamin Greene

    Date parameter / calculated field help

    Mikey Michaels
    Mikey Michaels Sep 27, 2016 12:25 PM

      Hello All,

      I hope this message finds you well.

      On the attached, I need some help with my date parameter. In my source file, I have the following string for my date field (YYYY0MM). So, I created two calculated fields to separate the string (Fiscal Year Calculated) and (Fiscal Period) - both utilize the left or right function.

       

      Anyways, here is the main issue-

      I have two measures that require a different date computation. The first is Seats UM, which is a point in time calculation. I need to find the COUNT of seats UM at quarter-end. Because I have no Fiscal Quarter field, I had to manually create one - called Fiscal Quarter - Calculated Field).

       

      IF [Fiscal Period] = "03" THEN "1"

      ELSEIF [Fiscal Period] = "06" THEN "2"

      ELSEIF [Fiscal Period] = "09" THEN "3"

      ELSEIF [Fiscal Period] = "12" THEN "4"

      END

       

      This works great, however, when I add the next measure - Revenue - I get an expected incorrect result. Here this is not a point in time calculation. I need to calculate the SUM of revenue for the entire quarter (fiscal periods 1 through 3 would be Q1, 4-6 would be Q2, etc. Any idea on how to adjust my date parameter and/or calculated fields to meet both these conditions?

      I do not want to have two separate date parameters as this would surely cause confusion.

       

      Any and all help is much appreciated!

       

      Cheers,

      Mikey

        • 1. Re: Date parameter / calculated field help
          Simon Runc
          Simon Runc Ambassador Sep 27, 2016 12:43 PM (in response to Mikey Michaels)

          hi Mikey,

           

          So it's hard to be exact without seeing and example of your data (ideally a packaged workbook)...but would this work? (I'm assuming the parameter is the numbers 1-4, representing the selected quarter)

           

          First I'm going to alter the parameter as follows

           

           

          Notice that the numbers (the value the parameter returns) is the Period, but the user sees the Alias (Quater 1, Quarter 2...etc.)

           

          I'm then also going to create an Integer version of your Fiscal Period

          [Fiscal Period - INT]

          INT( [Fiscal Period])

           

           

          Then, create 2 new fields which return the required result for each, by coding the filtering in the calculation...

           

          [Seat UM - Selected Quarter]

          IIF([Fiscal Period - INT] = [Select Quarter], [Seat UM], NULL)

           

          and bring this in as a COUNT

           

          [Revenue - Selected Quarter]

          IIF([Fiscal Period - INT] <= [Select Quarter]  AND [Fiscal Period - INT] >  [Select Quarter] -3, [Revenue],NULL)

           

          and bring this in as a SUM

           

          Then as the user selects the Quarter, one will bring back just a COUNT of Seat UM for the last period of the quarter, and SUM([Revenue]) for the whole quarter.

           

          btw I may not have all the correct brackets...etc. (I rely on the editor to spot such things for me!)

           

          Hope that helps, but let me know if not.

          1 of 1 people found this helpful
          • 2. Re: Date parameter / calculated field help
            Benjamin Greene
            Benjamin Greene Sep 27, 2016 12:48 PM (in response to Mikey Michaels)

            Since you only want to have one Fiscal Quarter field, here is what I would do:

             

            1. Right-click Fiscal Period and select Change Data Type>Number (Whole)

            2. Edit Fiscal Quarter so it is

            IF [Fiscal Period]<=3 THEN "Q1"
            ELSEIF [Fiscal Period]<=6 THEN "Q2"

            ELSEIF [Fiscal Period]<=9 THEN "Q3"

            ELSE "Q4"
            END

            3. Create a Quarter-End Seats UM calculation that is

            COUNT(IF [Fiscal Quarter]="Q1" AND [Fiscal Period]=3 THEN [Seats UM]

            ELSEIF [Fiscal Quarter]="Q2" AND [Fiscal Period]=6 THEN [Seats UM]

            ELSEIF [Fiscal Quarter]="Q3" AND [Fiscal Period]=9 THEN [Seats UM]

            ELSEIF [Fiscal Quarter]="Q4" AND [Fiscal Period]=12 THEN [Seats UM]

            END)

             

            Now, the Quarter-End Seats UM calculation will only have values for the last month of every quarter, and you will be able to sum revenue across every month within a given quarter.

            2 of 2 people found this helpful
            • 3. Re: Date parameter / calculated field help
              Mikey Michaels
              Mikey Michaels Sep 27, 2016 3:17 PM (in response to Simon Runc)

              Ugh - sorry Simon

              I cannot believe I did not attach my sample workbook (now attached).

              Also, I believe I let out a key detail to my question - again sorry for this.

               

              On the attached, I have a parameter called "Select Date Level" -> this allows a user to look at the data by month and also by quarter. From looking at your solution, it looks like the quarter-end issue for Seats UM has been solved - thank you!, but how can I adjust your solution so when my user select "Month" they will see the sum of Revenue and Seats UM for all periods (201601 -> 201612)? Below is what I expect the result to be when selecting Month and Quarter:

               

              Correct result when selecting Month from "Select Date Level"

               

              Correct result when selecting Quarter from "Select Date Level"

               

              • 4. Re: Date parameter / calculated field help
                Benjamin Greene
                Benjamin Greene Sep 28, 2016 7:35 AM (in response to Mikey Michaels)

                My original solution still works for what you are trying to achieve, just with one minor change. Make the formula in step 3 this instead:

                 

                IF [Parameters].[Select Date Level]="Month"

                THEN SUM([Seats UM])

                ELSE SUM(IF [Fiscal Quarter - Calculated Field]="Q1" AND [Fiscal Period]=3 THEN [Seats UM]

                ELSEIF [Fiscal Quarter - Calculated Field]="Q2" AND [Fiscal Period]=6 THEN [Seats UM]

                ELSEIF [Fiscal Quarter - Calculated Field]="Q3" AND [Fiscal Period]=9 THEN [Seats UM]

                ELSEIF [Fiscal Quarter - Calculated Field]="Q4" AND [Fiscal Period]=12 THEN [Seats UM]

                END)

                END

                 

                And then also remove that Select Date Level filter and you will get the numbers you are expecting.

                2 of 2 people found this helpful