6 Replies Latest reply: Feb 8, 2016 3:30 PM by Manish Kachhia RSS

    Total YTD Sales filtered by dimension

    Crystle Stamper

      I have a straight table with Dimensions, Company, Commodity, Item, Item Description.

       

      I then have 4 expressions

      Item Qty = Qty bought by Company on row

      YTD Sales = Sum of Sales by Company on row

      Total Item Qty = Total Qty of Items bought by all Customers

      Total YTD Sales = Total Sum of Sales for CFY bought by all Customers

       

      I can get all of these formulas to work correctly, the only problem is, when I select a Customer, it filters the 'Total YTD Sales'. But If I put a '1' in the formula (set analysis) to keep it from changing, then it doesn't filter the table for the Customer, it will still show all Customers in the table, even though the sum is correct.

       

      Here is my formula for the Total YTD Sales:

      Sum( {<INVC_FYADJUST={0} >} TOTAL <CUSTOMER_ID, ITEM> SHIP_ORDER_AMT)

      (the INVC_FYAdjust is a field built by the calendar to show the current fiscal year for 0, prior year is 1, and so on)

       

      This formula works if I select an Item or have nothing selected. But if I try to select a Customer, it filters the sum (Total YTD Sales) by the customer (and the table). But I only want it to filter the table to show all items for that Customer, but still have the Sum for this field show the Sum for ALL customers.

       

      Any help would be greatly appreciated. Thanks!