Changing Date Aggregation Dynamically

Question & Answer
, ,
1

I have created a parameter to show Monthly, Quarterly, Yearly numbers in my report

image
image1387×549 34.9 KB

But the period which is displayed in column has the same aggregation for each of the 3 parameter values. If the user selects Quarterly, I want to show it as Q1 instead of Jan. I am able to do it by creating a string calculated field. But, with that field I am not able to calculate Period Over Period % Change. It works fine if I use the date column and stops working if I bring in the string column.

image
image1469×732 46.1 KB

image
image1479×755 47.8 KB

I can’t use rule based rendering also because I am already using it for another parameter and it doesn’t support multiple conditions.

2

Thank you for your inquiry !

What I understand from your question:

  • Your analysis functions as expected with the date column
  • (from last screenshot) When you add a text field to display the date in ‘YYYY - MM’ format instead of ‘MMM D, YYYY’ format the ‘% Change’ field no longer functions as you expect

Format the ‘period_start_date’ field to a custom value (in your case, YYYY - MM)
detail steps can be found here

3

Hi @RobHendriks

This will not solve my problem as I want user to see Q1 - 2022, Q2 - 2022, etc. … when they select Report Type as Quarterly

4

Can you instead of doing a period over period change do a lag function.

(sum(actual_value)-lag(sum(actual_value,[period_strart_date ASC], 1)))/lag(sum(actual_value,[period_strart_date ASC], 1))

You might need to alter the period_start_date to trunc based on the parameter.

5

Yes @Max, I had used this way. I used the lag function in my SQL Query to bring actual_value_prev_period column and then used it to calculate Period over Period Change.

1 Like