Using total with column name instead of fields

[#1]

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

[hdonald]

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

[jonathandienst]

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

[#4]

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

[#5]

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