Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I'm trying to calculate some different results on the same graph: There's a category of articles (suppose 1 for simplicity), a set of articles belonging to that category and a set of agents that have to sell them in order to reach a target (global and personal).
Type of Articles, Agent and SubType are dimensions, Sales and Target fields, all other columns are calculated: I've attached the file with the table.
Type of Articles | Agent | SubType | Sum(Sales) | Sum(Target) | Delta | TOT SALES | TOT TARGET | TOT DELTA | Negative Delta | Total Negative Delta | Fine | Total Fine |
Type 1 | Agent A | Sub1 | 10 | 34 | -24 | 146 | 211 | -65 | -65 | -70 | -22,29 | -117,21 |
Agent A | Sub2 | 30 | 6 | 24 | 145 | 70 | 75 | 0 | 0 | 0,00 | -117,21 | |
Agent A | Sub3 | 320 | 32 | 288 | 376 | 167 | 209 | 0 | 0 | 0,00 | -117,21 | |
Agent A | Sub4 | 3 | 78 | -75 | 102 | 191 | -89 | -89 | -99 | -67,42 | -117,21 | |
Agent A | Sub5 | 23 | 45 | -22 | 75 | 115 | -40 | -40 | -32 | -27,50 | -117,21 | |
Agent B | Sub1 | 53 | 99 | -46 | 146 | 211 | -65 | -65 | -41 | -72,93 | -247,72 | |
Agent B | Sub2 | 57 | 31 | 26 | 145 | 70 | 75 | 0 | 0 | 0,00 | -247,72 | |
Agent B | Sub3 | 24 | 56 | -32 | 376 | 167 | 209 | 0 | 0 | 0,00 | -247,72 | |
Agent B | Sub4 | 54 | 78 | -24 | 102 | 191 | -89 | -89 | -14 | -152,57 | -247,72 | |
Agent B | Sub5 | 25 | 35 | -10 | 75 | 115 | -40 | -40 | -18 | -22,22 | -247,72 | |
Agent C | Sub1 | 83 | 78 | 5 | 146 | 211 | -65 | -65 | 5 | -65,00 | -194,00 | |
Agent C | Sub2 | 58 | 33 | 25 | 145 | 70 | 75 | 0 | 0 | 0,00 | -194,00 | |
Agent C | Sub3 | 32 | 79 | -47 | 376 | 167 | 209 | 0 | 0 | 0,00 | -194,00 | |
Agent C | Sub4 | 45 | 35 | 10 | 102 | 191 | -89 | -89 | 10 | -89,00 | -194,00 | |
Agent C | Sub5 | 27 | 35 | -8 | 75 | 115 | -40 | -40 | -8 | -40,00 | -194,00 |
At the end of each period if the global article target is reached (the sum of single agent target), all right, otherwise the global delta has to be shared between the agent that has not reached the personal aim. In this way, if the single agent delta for aricle A is -10, 5 and -20, the first and the third have to share -25 according to 33% and 66%.
Everything works fine except the red column (total negative delta), that is creating some problems: it has to be the sum of (only) negative delta for each article, regardless of agent.
- sum( total <SubType> [Delta]) doesn't work because Delta is not a field but a column name. I've tried with $ expansion but without success...
- rangesum doesn't support 'total'
Any idea?
Thanks in advance
Luca
Hi,
You could try using the column labels in your expressions - although not dimensions they should still work e.g.
=if([Negative Delta]<0,sum(Total <SubType> if(Sales-Target<0,Sales-Target,0)),0)
as in attached example, [Negative Delta] is a column name.
They figures don't match your spreadsheet exactly as it seems to be selecting only 2 Agents at a time, e.g. cell K6 shows -32 rather than
-40 as the Agent C's value is not included. Are only pairs of agents to be compared ?,
Regards,
HD
Luca
Unfortunately you cannot use column names in aggregate functions, which makes sense, if you think about it.. In your case, you will need to replace the reference to Delta with the calculation the produced Delta in the first place.
Regards
Jonathan
Hi Jonathan and HDonald,
thank you for your suggestions: at the end I've understood that I cannot use sum[Delta] because are not linked with dimensions (as soon as possible I want to go in deep with this issue), and that values on that column has to be treated separately cell by cell.
I've used formulas based on rowno() and noofrows() in order to create a column with incremental values per agent and another column with total per agent repeated on each row. It's a brute force approach, but it works fine!
Thank you
Luca
You need to use the aggr() function to group your field values.
Try something like aggr(sum(total <SubType> Delta), SomeDimensions)