I need to write an expression to show: sum(SalesQuantity) but to ignore a dimension (PriceOverride). I have not not a clue how to write this, can anyone help please?
I don't think i explained what i wanted very well... I have attached a sample... If you look at the sample, you can see that it is displaying PriceOverrides for both 'yes' and 'no'. I only want it to display the sales relating to 'yes'. With it how it is, the 'Total Sales' figure is correct. i.e.
3 6 9 2
I can't see a way of hiding the PriceOverrides = 'no' unless i add in a calculated dimension IF((PriceOverride = 'yes' ), PriceOverride) then ticking 'Suppress null vales', but if i do that, the 'Total Sales' change and only total the Reduced Sales. What i want is to only display sales relating to PriceOverrides = 'yes' but for the 'Total Sales' to be unaffected and actually show the full sales as it does when both PriceOverrides = 'yes' and 'no' Do you know how i can achieve this please? Thanks for your help Chris
hopkinsc wrote:Do you know how i can achieve this please?
Had you applied my fix to the sample file you posted, and not to some new file, you would have seen that it worked. Your original example had three expressions. The expressions I gave nulled out the values if the price override isn't 'yes'. Therefore those rows were suppressed.
But instead you made a new example with FIVE expressions. So now you need to do the same thing for the expressions you've added in your new example. In other words, follow the pattern indicated by the three expressions I gave you:
if(PriceOverride='yes', YourCurrentExpression)
Also, you have an expression that is very specifically looking for a price override of 'no'. That needs to be removed, as it is meaningless if you're intending to remove those rows.
Thanks for your help, sorry about the expressions, i meant to disable them before posting the example.
I have copied what you have posted and it works great for the current selections. But i have noticed that the total sales are wrong if i clear the selections and look at more than one product at a time. As soon as i select ONE product again, the sales are correct.
I have attached another sample, I can't figure out ehy it is correct when looking at individual products but incorrect when looking at multiple products.
if(PriceOverride='yes',sum({<LineType-={'V'}>} total <Customer> SalesQuantity))
...says we want to ignore all dimensions but Customer. So one of the dimensions we're ignoring is the product, so that's why you're seeing what you're seeing. I only listed Customer because you specifically said you wanted to separate the customers, and because that was sufficient to give the correct results in your original sample file. It sounds like you want to add at least add Product to that list. There may be other dimensions from your chart that you want to include as well. Perhaps even ALL of them EXCEPT for PriceOverride:
You've also now removed PriceOverride as a dimension, which will probably cause serious problems with the if(). If any of these rows have both price override 'yes' and price override 'no', then they'll be excluded. You won't see just the 'yes' data. If you don't want to see the price override column, hide it on the presentation tab instead of removing it, because how I wrote the expressions depends on it. There's probably also a solution that doesn't require it, but I don't feel like starting over.
I recommend reading help or the reference manual or whatever to try to figure out what the code I posted is doing and how it's doing it. That should help you adapt it to your real situation as you add other dimensions and expressions and rows of data that weren't in the original example.
