4 Replies Latest reply: Nov 14, 2014 12:02 PM by Tomás Lima

# Using total with column name instead of fields

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?

Luca

• ###### Re: Using total with column name instead of fields

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

• ###### Using total with column name instead of fields

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

• ###### Using total with column name instead of fields

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

• ###### Re: Using total with column name instead of fields

You need to use the aggr() function to group your field values.

Try something like aggr(sum(total <SubType> Delta), SomeDimensions)

expression name showing as a bad field name