6 Replies Latest reply on Mar 13, 2017 7:07 AM by sam.hong.0

# Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Hello,

I am having a little difficulty in having my calculation produce the right result as well as would like feedback on seeing if the calculation is efficient.

Issue

(1) My calculation for "Year: Change" is valid, but for rows where it is NULL, it does not say "No Change" as indicated by my calculation.

Request

(1) Is it possible to create a parameter that would allow a user to pick and choose which years they want to pick? For example, I have number of events hard coded per each year (2012, 2011) but I would like to be able to allow a user to select any year and have the table updated.

(2) I am having trouble finding a way to convert this comparison to Quarters and Months. Would this be possible?

• ###### 1. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Hello sam,

The attached workbook might meet your request 1.

I still left the NULL for [YEAR: Change] for it's divided by zero.

And a question for request 2.

Do you want to compare month/quarter with last month/quarter or with the same month/quarter of the previous year?

Regards

Lei

1 of 1 people found this helpful
• ###### 2. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Lei,

Thank you!!! Request one was exactly what I was hoping for!

And for Request 2, I would ideally like to be able to compare Current Quarter to Last Quarter (Q1 2017 vs Q4 2016) and also Current Month to Last Month (March 2017 vs. February 2017).

However, if you also happen to know how to do this for the same quarter and month in the previous year, that is more than welcome as well!

Sam

• ###### 3. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Hello Sam,

Happy to hear that

I suppose the four variations of comparisons are all possible, with more calculated fields.

Sorry that I may not have enough time for this today.

Regards

Lei

• ###### 4. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Thank you, Lei!

Looking forward to it!

• ###### 5. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Hello SAM,

Sorry for having kept you waiting.

I'm back with the attached sample workbook.

Now we have three parameters.

Parameter 1. Select Comparison by

Choices are,

• Year
• Quarter
• Month
• Quarter vs Last Year
• Month vs Last Year

Parameter 2. Select Year

When "Year" is selected in Parameter 1, this parameter works.

Parameter 3. Select Year-Quarter

When "Month" or "Month vs Last Year" are selected in Parameter 1, this parameter works.

I've prepared everything ready for "Year", "Quarter" and "Quarter vs Last Year" in Parameter 1.

Please give it a try at setting up "Month" and "Month vs Last Year" and enjoy the process

Regards

Lei

1 of 1 people found this helpful
• ###### 6. Re: Calculations with Percentage Changes and Mapping (WORKBOOK ATTACHED)

Hello Lei,

Sorry for the late response but thank you so much for looking into this and providing the steps needed for me to figure out how to do the month based on what you created. I am going to work on it this week and let you know if I have any questions!

Sam