bar chart with two measures and date dimension not matching one of the measures
Hi, I need to create a bar chart with two measures - First counts quota - Second counts attainment
The quota has a separate date field from the opportunity table where the attainment is counted. For the dimension I'm using quota_start_date, but the opportunity table is not connected to the quote table, and it uses close date as a date field.
The dates are in the same format and I'm using an expression like this 1. =sum(quota_amount) - orange on the attached chart 2. =sum({<quota_start_date=,CloseDate = P(quota_start_date)>}opportunit_amount) - blue on the attached chart but the effect is that the quota numbers are displayed correctly, and for the second expression I see the same value in all dates.
You should remember that Set Analysis expression that you are using to tie the two dates together, cannot be sensitive to your Dimension values. It is being evaluated globally, outside of your chart. So, in the second measure you are getting the sum of all opportunities where Close Date exists in the possible values of the field quota start date, disregarding of the chart dimensions.
The correct solution would involve some data modeling - you need to find a way to link the two tables correctly by building valid associations between quotas and opportunities.
Otherwise, if you replace your Set Analysis with an IF condition, then the logic would work, but comparing dates from two tables that are not linked, would cause a Cartesian join between two table, which will cause serious performance issues (assuming that your data is not small).
You should remember that Set Analysis expression that you are using to tie the two dates together, cannot be sensitive to your Dimension values. It is being evaluated globally, outside of your chart. So, in the second measure you are getting the sum of all opportunities where Close Date exists in the possible values of the field quota start date, disregarding of the chart dimensions.
The correct solution would involve some data modeling - you need to find a way to link the two tables correctly by building valid associations between quotas and opportunities.
Otherwise, if you replace your Set Analysis with an IF condition, then the logic would work, but comparing dates from two tables that are not linked, would cause a Cartesian join between two table, which will cause serious performance issues (assuming that your data is not small).
