4 Replies Latest reply: Nov 13, 2010 6:58 AM by Chris Hopkins RSS

    Stuck on Total Sales!!!

    Chris Hopkins

      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...

      error loading image


      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?