total data above average figures

[#1]

Hi I have a pivot table which has columns that show the average of item costs by a dimension.  I need an additional column which will then show the cost differences of those items above the average cost, ie sum(If(Cost > avg(COST), COST - avg(COST)), this expression doesn't work and I have tried a variety of other ways using aggr but all with no joy.

I would be grateful if anyone out there can point me in the right direction

Thankjs

[Jason_Michaelides · Accepted solution]

Try:

sum(If(Cost > aggr(avg(COST),YourDimension), COST - aggr(avg(COST),YourDimension)))

or if that doesn't work try:

sum(If(Sum(Cost) > aggr(avg(COST),YourDimension), Sum(COST) - aggr(avg(COST),YourDimension)))

Hope this helps,

Jason

[#3]

Completely agree with Jason's (Set) expression.

Seems to work with my sample.

[#4]

Hi thanks for your reply, the first example returns figures but are too low.  I dont think the second example would be correct as that is not comparing the low level costs instead it is comparing the sum of the cost, and as such brings back nothing.

[Jason_Michaelides]

You might need to add another dimension to the Aggr() functions...can you provide a sample app?

[#6]

Could maybe do an expression similar to the following attached.

[#7]

Hi Thanks for your reply,

the problem with your example is I don't want to see each individual item, only the id.

thanks

[#8]

Hi unfortunately I dont think I can provide an example as the app is currently 3 gig in size and has confidential information.  I can maybe give you some more detail which may help.

I have a fact table, where each person has costs of various items.  An Item ID joins this table to the cost items table, in the cost items table there is further information about the cost items.  I.e descriptions, cost groups etc.  The fact table has a unique Id for each person so I thought I should maybe include that in the aggregation but that didn;t work either.

I have actually moved on from summing the difference in values above the average to instead having a target figure for each cost item.  This is used as an inputfield and so when the figure is changed in the table I want the figures to calculate to be any costs above the the target figure.  Again this is now generating figures but appear to be extremely low. The expression I am using is

=Sum(If(COST>aggr($(Var_CostVariationTarget),ITEMID),COST-aggr($(Var_CostVariationTarget),ITEMID)))

Thanks in advance

[Jason_Michaelides]

If you can take the time to construct a sample of data it would be easier to help you. Preferably in a sample QV doc or at least provide a couple of sample data tables (don't have to have many rows) and your desired outcome.

[#10]

Hi Jason,

I managed to sort out the issue, thanks for your help.  Just to give a bit of a background I was referencing multiple variables to derive the figures.  The actual variables didnt correlate to the low level data, so I basically inserted a total property in each variable and it works now.  Once again thanks for your help, your original expression put me on the correct path.

thanks