Hi Everyone!
I am looking to replace the row names on a pivot table to show quarters(Q1, Q2, Q3 and Q4) which can be changed dynamically using the Reporting period parameter control. As indicated in the image below i have the Quarters as Jan 2023, Apr 2023 and Jul2023 which i would like to change to show Q1, Q2 and Q3 which will mirror the all metrics like Impressions QoQ and MoM which are changed with the Reporting Period control Q for Quarters and M for Month.
Any help will be greatly appreciated.
Thanks
Kevin
Hello @wangerkc - Thank you for posting your query. This is an interesting problem and I have tried to replicate it at my end. I believe, this can be done by combining parameters and calculated fields. Trying to provide a step by step guide below on my approach. Hope this follows.
Step 1 : Create a parameter name paramPeriod. Put that into a control and have 2 hardcoded values listed in the control - Quarter and Month. Specify “Quarter” as the default value in the parameter definition.
Step 2 : Create five calculated fields named Order Month, Order Quarter, Period, Previous Period Qty and Change. In my example dataset, I am dealing with Quantity measure instead of impressions. Also, the original dataset had Order Date, so had to create Order Month and Order Quarter fields. Some of these fields have been calculated using the parameter that was created in the previous step. Feel free to tweak that according to your scenario. Sharing the definition of the calculated fields below :
Step 3: Project the fields properly along with the parameter control in the Table Visual to get your desired output. Showing you both the outputs while Quarter and Month has been selected in the control respectively.
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
1 Like
Hey @sagmukhe - Thanks very much for your reply i tried the suggested method which worked totally and i have the $Reporting as M and Q fine as indicted:
But the challenge i am currently facing is that i am using this 2 calculated fields to populate the pivot table as this is a Monthly Campaign Deck and need to show the MoM% and QoQ% as indicated in the third image within a selected date range:
Currently the Reporting Period is like this which returns a date format and makes the periodOverPeriodLastValue and periodOverPeriodPercentDifference work:
But when i try to use Report 1 to calculate periodOverPeriodLastValue and periodOverPeriodPercentDifference it gives me an error as it’s not a date format
Here is the error that is getting generated:
Expression periodOverPeriodLastValue(sum(impressions), {Report 1}, MONTH, 0) for function periodOverPeriodLastValue has incorrect argument type periodOverPeriodLastValue(Number, String, , Number). Function syntax expects Numeric, Date
Numeric, Date, Period, Numeric.
Any suggestion would be greatly appreciated. Thanks
@wangerkc - Thanks for the response. I will think on this. However, wanted to check if you took a look at the “Change” calculated field where I calculated this Mom/QoQ items dynamically based on the input without using the periodOverPeriod calculations. I was wondering if there’s any way for you to take that route to get rid of this problem. Thank you!
@sagmukhe - Thanks again for such quick response. I actually tried just the steps 1,2 and 3 as i already had all the calculated fields for the metrics and i have nearly 20 each metric plus MoM, QoQ and Year:
But now that you have mentioned i actually think this might work because it looks more flexible than PeriodoverPeriod functions. Just a quick disclaimer i am an apprentice so this is my second dashboard to work on, hence why i am not familiar with a lot of the functions so learning as i go. I will actually now try Steps 3 and 4 because yours look much more easier and i have noticed that they do exactly what i have done so far with my approach.
Thanks very much for the help i will let you once am done.
1 Like
@sagmukhe . I also have one more questions if you don’t mind please: I have this Metric OOR% which is selected on the Metric control and calculated as indicated on the calculated field below the line chart :
The issue i have is i can’t get it to show the percentage symbol as i have set default format for the calculated field to numbers as i need this for most of the others metrics. Similar to this image:
@wangerkc - Thank you for your message. On a quick look at the problem statement, one solution I can definitely think of is to leverage the “Free Form Layout” and the conditional visual rendering feature. You can create multiple similar line charts with same size and dimensions and lay one over another. Then you can utilize the parameter to configure the rule to Show/Hide the visual conditionally based on the parameter Value. Sharing you a link and a sample implementation tried from my end below for your reference. Hope this helps!
Sample Implementation:
