2 Replies Latest reply on Mar 27, 2017 10:22 AM by Maddy Pena

    Custom time periods on parameters

    Maddy Pena

      Hello guys!

      I need to show information for different time periods (year, semester, quarter, month), and I wanted to give the user the control over the visualization through a parameter.

      I have values for Actual and Rolling Forecast, so I’m linking the parameter value to the formula to calculate when I should present the actual or forecast values on the graph



      That same parameter value is linked to the calculation of the time period shown. This works well enough for year, quarter and month (the only downside is the name of the time period on the graph).




      The problem with this is that “semester” is not a valid a parameter on the DATETRUNC function and the visualization stops working.


      I’m aware of the creation of a hierarchy (which I tried) but I think it would be problematic for the user to use the graph controls to drill down in the graph.




      Does anyone know another way to achieve the semester option to be valid when selected from the parameters?

      Or even the hierarchy to be shown as a quick filter?


      Thanks a lot!

        • 1. Re: Custom time periods on parameters
          Okechukwu Ossai

          Hi Maddy,


          Is this what you are looking for? There is another way to achieve the semester option. You will need to manually calculate the equivalent of Datetrunc for all the parameter values using [Month_Year]. Then repeat this using Today(). Finally, update [Color Value Category] and [Value to report] calculated fields .


          I kept my code consistent with your semester definition. Semester 1 from Jan to Jun and Semester 2 from Jul to Dec. Below is how the semester parameter selection looks. See attached workbook.



          I created 10 calculated fields. These are the manual equivalents of the DATETRUNC function. I broke them down into small chunks for easy comprehension.


          1. [Datetrunc - Month]



          2. [Datetrunc - Quarter]

          IF MONTH([Month_Year]) <= 3 THEN "Jan"+"-"+RIGHT(STR(YEAR([Month_Year])),2)

          ELSEIF MONTH([Month_Year]) <= 6 THEN "Apr"+"-"+RIGHT(STR(YEAR([Month_Year])),2)

          ELSEIF MONTH([Month_Year]) <= 9 THEN "Jul"+"-"+RIGHT(STR(YEAR([Month_Year])),2)

          ELSEIF MONTH([Month_Year]) <= 12 THEN "Oct"+"-"+RIGHT(STR(YEAR([Month_Year])),2)



          3. [Datetrunc - Semester]

          IF MONTH([Month_Year]) <= 6 THEN "Jan"+"-"+RIGHT(STR(YEAR([Month_Year])),2)

          ELSE "Jul"+"-"+RIGHT(STR(YEAR([Month_Year])),2) END


          4. [Datetrunc - Year]



          5. [Datetrun - Mth - Today]



          6. [Datetrun - Qtr - Today]

          IF MONTH(TODAY()) <= 3 THEN "Jan"+"-"+RIGHT(STR(YEAR(TODAY())),2)

          ELSEIF MONTH(TODAY()) <= 6 THEN "Apr"+"-"+RIGHT(STR(YEAR(TODAY())),2)

          ELSEIF MONTH(TODAY()) <= 9 THEN "Jul"+"-"+RIGHT(STR(YEAR(TODAY())),2)

          ELSEIF MONTH(TODAY()) <= 12 THEN "Oct"+"-"+RIGHT(STR(YEAR(TODAY())),2)



          7. [Datetrun - Sem - Today]

          IF MONTH(TODAY()) <= 6 THEN "Jan"+"-"+RIGHT(STR(YEAR(TODAY())),2)

          ELSE "Jul"+"-"+RIGHT(STR(YEAR(TODAY())),2) END


          8. [Datetrun - Year - Today]



          9. [TimePeriod]

          IF [Period] = "year" THEN DATEPARSE("MMM-yy",[Datetrunc - Year])

          ELSEIF [Period] = "semester" THEN DATEPARSE("MMM-yy",[Datetrunc - Semester])

          ELSEIF [Period] = "quarter" THEN DATEPARSE("MMM-yy",[Datetrunc - Quarter])

          ELSEIF [Period] = "month" THEN DATEPARSE("MMM-yy",[Datetrunc - Month])



          10. [TimePeriod Today]

          IF [Period] = "year" THEN DATEPARSE("MMM-yy",[Datetrun - Year - Today])

          ELSEIF [Period] = "semester" THEN DATEPARSE("MMM-yy",[Datetrun - Sem - Today])

          ELSEIF [Period] = "quarter" THEN DATEPARSE("MMM-yy",[Datetrun - Qtr - Today])

          ELSEIF [Period] = "month" THEN DATEPARSE("MMM-yy",[Datetrun - Mth - Today])



          11. [Color Value Category] and [Value to report] were updated


          Let me know if this helps.



          • 2. Re: Custom time periods on parameters
            Maddy Pena

            Ossai, thank you very much for your help!

            This is what I needed, the only thing is that I cannot achieve the proper axis categories names when a different period is selected, but I guess is one of the shortcomings of custom time periods.