Using a the value of a field to determine which field is totalled in an expression

[felix18807]

I know I'm going to have difficulty explaining this one - apologies

I have to run a series of reports where the value being added up is dependent on the report/dimension being selected.

So for a where

Report = A / Distributor = B / Site = C    the expression is Sum ([Spend X])

but where
Report = A / Distributor = B / Site = D    the expression is Sum ([Spend Y])

I have got as far as creating a table that holds the Spend fieldname for each Report / Distributor / Site so I have which field should be totalled held in a field. What I don't know is if you can use this dynamically in the expression.

Has anyone got any experience of this?

Many Thanks

[marcus_malinow]

Hi Richard,

this doesn't sound like anything too tricky. You have a couple of options:

In your load script, add a couple of flags like this:

If(Site='C',1,0) as SpendXFlag,

If(Site='D',1,0) as SpendYFlag,

then your expressions is:

Sum([Spend X] * SpendXFlag) + Sum([Spend Y] * SpendYFlag)

Or...

You could use this expression:

Sum(if(Site='C',[Spend X],If(Site='D',[Spend Y],0)))

I'd favour the first option personally.

Marcus

[felix18807]

I thought this might be what was needed.

Unfortunately there are a lot of variations in the Report / Distributor / Site so I was hoping to be able to use something like SUM($(ValueFieldName)) - which doesn't seem to work (there is a table that links by a Report+Distributor+Site key that has a field "ValueFieldName" which has the name of the field that I want to total).

[marcus_malinow]

Hi Richard,

do you have an example doc you could upload? I think there's probably a way to do this in a similar way to the way you suggest. Probably this would use the AGGR function to set up a list of sums for each ValueFieldName, then sum those.

Marcus

[felix18807]

I've thrown this together. I hope it helps...

[marcus_malinow · Accepted solution]

Hi Richard,

try this expression

SUM($(=CONCAT(DISTINCT R_Field, '+')))

[felix18807]

That is genius! Exactly what I needed!

Many many thanks Marcus