Help with displaying total sales qty

[hopkinsc]

Hi,

I am trying to get a total of sales qty.

Here is what i need...

I am displaying all products that have been sold as 'Reduced to Clear', but i also want to display a column showing ALL sales (at reduced or normal price)

There is a field called 'PriceOverride' which states 'yes' or 'no' if the product was sold reduced to clear or not. I have added a calculated dimension to only display PriceOverride=yes and to hide null values. but when i do that the total sales only show reduced sales.

here are screenshots of what i have done

Above shows 'hide null values' unticked but the sales quantity is correct. i.e. sales value =3, reduced sales QTY=1

Above shows 'hide null values' ticked, so it only displays reduced to clear sales, but the sales value =1 when i want this to display total sales which is 3.

the expession i am using for Total Sales is: sum( {$<LineType -= {V}, PriceOverride = {"*"}>} SalesQuantity )

which i believe means sum sales quantity but deduct LineType V and include ALL PriceOverrides (yes and no).

Can anyone help please?

[pover]

When suppressing the null values, the expression total will ignore the values returned by the null dimensions so you have to do something like below where you have one formula for the total and another for the rest of the table. We recognize the total row because it has dimensionality() equal to 0.

if(dimensionality()=0,sum({$<LineType -= {V}>} Total SalesQuantity ),sum({$<LineType -= {V}>} SalesQuantity ))

Regards.

[hopkinsc]

Hi Karl, thanks for your reply..

That worked, but i have just realised that the user would be looking at more than one product at a time, so i would want to 'No Totals' selected on the expression otherwise it would display total sales for all products displayed.

Instead i would want the 'Total sales' to be on the same line as the product.

So above, i waould want the 'Sales Quantity' to display 3 not 1 instead of a total on top.

Is this possible please?

Thanks

[hopkinsc]

Hi,

Any more help with this anyone?

Thanks

[pover]

Try the following expression

sum({$<LineType -= {V}>} Total <[Prod Desc]> SalesQuantity ))

Regards.

[hopkinsc]

Hi, that didn't work either.

I have tried

sum({$<LineType -= {V}>} Total SalesQuantity ))

which works if i am only looking at ONE product, but if i there is more than one product displayed, the expression totals up ALL sales for ALL products displayed.

see my example below..

Can anyone tell me how to display the total sales per product?

[hopkinsc]

Can anyone help with this please?

[pover]

But don't just use Total. Notice the example I put has another field after Total <[Prod Desc]>, which is the field you are going to limit your total to. In your case, you can also try Total <[Product Code]>. In that way it won't give you the grand total when nothing is selected.

Regards.

[hopkinsc]

Thanks for your help Karl, I did try that but it still didn't work when i have mutiple products shown.

I have managed to do it now though by using the expression:

sum(aggr(sum({$<LineType -= {V}>} total <EAN> SalesQuantity),[Cust Name],[Product Code],[Prod Desc],EAN,Size,PriceOverride))

Thanks