Skip to main content

Hi Pigment Community!
I'm currently facing a challenge and would appreciate your advice.

The Issue:
I need to combine two metrics from different applications to create a comparison view for my users. Here’s the setup:

App 1: Holds the visual displayed to users and includes a metric titled “Actual & FCST # SQL -> SQP - NBiz.”
App 2: Contains a metric titled “# FCST SQL by SQL date - NBiz.” This metric has been shared with App 1, so I can access it there.
The problem arises when I try to bring these two metrics together. Since the applications have different time dimensions, I end up with two sets of time dimensions in my final metric, causing the values to not calculate correctly.

Additional Context:
I've separated the time dimensions in these applications because they have different period lengths.
However, the specific data I want to combine should fall under the same period of time.
Possible Solutions:
I’m considering using the remove modifier or specifying by: month] to align the metrics on the month dimension. However, I’m not sure how to implement these ideas effectively, given the two different time dimensions.

The Question:
What can I do to ensure that both metrics align on the same time dimension in the final metric? I’m looking for solutions that would help me avoid the issue of having two conflicting time dimensions.

 

If you require any futher information to better understand the issue please let me know. 

Thanks in advance for your help!

App 1: Actual & FCST # SQL -> SQP - NBiz → native to the application (has its own calendar)

 

App 2: # FCST SQL by SQL date - NBiz → shared from a different application (has its own calendar)

 

App 1: GTM Rolling+Initial SQL by SQL date - NBiz

 

Hi @darious ,

I noticed that the metrics currently don't have the same number of dimensions. Your first metric has 3 dimensions, while the second one has 4 dimensions. Is the Month dimension the only one causing the discrepancy?

Regarding the month dimension issue, you could use the following formula:

ROUND(Actual & FCST # SQL -> SQP - NBiz,0) 
- 
ROUND(# FCST SQL by SQL date - NBiz,0))BY:Item(App 2.Month.Name,Month)]

The first part of the formula remains unchanged and uses the Month dimension from your App 1.

The second part now includes a BY modifier with the ITEM function to align the calendar from App 2 with the calendar from App 1. Assuming the month names are the same in both apps, and only the time horizon differs, the ITEM function will map the "Name" property from the App 2 Month dimension to the "Name" in the App 1 Month dimension.

Additionally, ensure that the range of your App 1 calendar is larger than that of App 2. Otherwise, some months may not be matched correctly.

I hope this clarifies things!
Let me know if I missed any part of your question.

Reply


Ask your question here!

Terms & ConditionsCookie settings