I have a bar chart that displays the target values for a sales employee per month. The input data for both target values and actuals is divided in two product groups, 'A' and 'B'.
So what I would like to have is target values bar for both groups together. But if the user selects either 'A' or 'B', the target values shall be adapted to only reflect the target value for the corresponding group.
Example:
A B
Target 100 50
Actuals 80 45
This works correctly in my solution for months where I have actuals for both groups 'A' and 'B'. The problem are months where is no data for either 'A' or 'B' products. This leads to an automatic filtering for the product group like here
Situation:
A B
Target 100 50
Actuals - 45
Displayed in the bar:
B
Target 50
Actuals 45
That is somehow logical, but without filtering for a product himself, the sales guy working with this dashboard will hardly understand why this month's target is only 50 and not 150 (overall).
Do you have an idea how to make it clear to the user, how the currently displayed target value is calculated? The chart is a grouped chart, is it possible to stack only this single bar to a combined target value with two different color? Any other approach?
If you concatenate the two tables, "Targetmonth" and "OLI.Month" become the same thing, so you need to load these fields (and perhaps other fields also) by renaming them, e.g.
Targetmonth as Month
OLI.Month as Month
When this is done, you can click on a month and get both targets and opportunities for that month.
the basic implementation is working now, many thanks.
But I have a strange behavior in my target values. They are twice as high as they should be. If i select a user and single month, it should be e.g. 101798,25, but the expression "sum(Targets)" returns twice as much.
In my table box, there are three lines for the month 201401, where I was expecting only two (for two different markets).
One way or another, you seem to have loaded the target values twice. Without the script or the app, it is impossible for me to say how that has happened.
.png "Former Employee"){kind=icon}