Total YTD Sales filtered by dimension

[crystles]

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!

[sinanozdemir]

Maybe this:

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

[crystles]

Thanks for the reply.

I have tried that formula, but it gives me the same result as the '1' in set analysis. It will keep the correct sum in the formula, but it wont filter the table to show only that Customer that is selected.

[MK_QSL]

I think for Total Sum of Sales for CFY bought by all Customers

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

and for ignoring selection of CUSTOMER_ID, use below

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

Are you sure you need <CUSTOMER_ID, ITEM>

[crystles]

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

if I use this formula it only gives me the entire total for everything for every row, that is why I have the <CUSTOMER_ID, ITEM> after the TOTAL

[sinanozdemir]

Do you think you can share some sample data or your app?

Thanks

[MK_QSL]

If you don't need entire total, what total you want?