Stuck on Total Sales!!!

[hopkinsc]

Hi all.

I am struggling to get an acurate Total Sales figure..

I am writing a wastage / reduction application, I need to have Total Reduced Sales Qty and Total Sales Qty (Which is sales at full and reduced price).

I have a field on my transaction table called 'PriceOverride' which states 'yes' or 'no' if the product was sold reduced or not.

For my app to only display reduced sales i have added a calculated dimension =IF((PriceOverride = 'yes' ), PriceOverride) and ticked 'Suppress When Value is Null' which then only displays REDUCED sales.

Then to get my REDUCED SalesQuantity i have added an expression Sum(SalesQuantity) which displays an accurate Sales Quantity (At Reduced Price) per product.

I then need to get a Total Sales Quantity (For Full price and Reduced sales)per product , I couldn't figure out how to do this as the SalesQuanity field is already being summed up, but because i had ticked 'Suppress When Value is Null', the NORMAL PRICE sales were no longer there, so someone on this forum suggested i use the expression :

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

This expression worked and i thought it had been solved, but at closer inspection today, it only works if i am viewing just one store ([Cust Name], If i view more than one [Cust Name] at a time, the 'Total Sales Quantity' is the total for ALL [Cust Names] See below...

If you look at the example above, Spar Chipping Sodbury sold 1 'Fresh Milk' reduced (RTC QTY) and total sales shows 649. 649 is actually the sales for all stores which are selected (118 stores), the correct figure for Spar Chipping Sodbury is 9.

Can anyone help me achieve the correct figure?

Thanks..

[#2]

Just a thought but wouldn't it be simplier to do the evaluation in the load and carry both values. In that way you wouldn't need to do the caluculations in every chart.

[hopkinsc]

Hi, thanks for your reply.

How would i write into the script something like IF priceOverride = 'yes' sum salesquantity AS Reduced QTY?

so i would get a new field called Reduced QTY which will show me only reduced sales qty's?

Thanks

[johnw]

hopkinsc wrote: How would i write into the script something like IF priceOverride = 'yes' sum salesquantity AS Reduced QTY?
so i would get a new field called Reduced QTY which will show me only reduced sales qty's?

In the script you're only looking at one row at a time, so it would be something like this:

if(PriceOverride='yes',SalesQuantity) as ReducedQuantity,

And yes, then ReducedQuantity would only have a value when PriceOverride='yes'. For any other value of PriceOverride, it would be null (null is typically better than 0 for a case like this).

[hopkinsc]

Thanks John. That worked great.