Change results in a straight table with drilldown..please help

[vtatarnikov]

Hello.

I have the straight table:

After clicking on the "shop" (Наименование), "production cost" (Себ-ть) changes:

or

etc.

"Cost"(Себ-ть) =sum(Aggr(sum([Количество реализации])*FirstSortedValue([Цена закупки], -[Дата цены]), id_Товара))

                     (it is =sum(Aggr(sum([amount realization])*FirstSortedValue([purchase price], -[pricing date]), id_Goods)) )

Other numbers are considered to be from the "cost". Changing amount "store" one, but the total amount of all the shops remained unchanged 44 333 955. By goods production cost does not change with drilldown.

Schema:

One store (Магазин) may correspond to multiple warehouses (Склады). Sin on the keys (Key_Дата_Склад_Товар its Key_Date_Warehouse_Goods, and Key_Дата_Склад its Key_Date_Warehouse) and function "FirstSortedValue".

Sorry, for my english. Thanks in advance.

[DavidFoster1]

Hi Vladislav

To help me understand... What are you using the 'FirstSortedValue' function for?

[vtatarnikov]

Hi David. One product, there are several wholesale prices, sorted by date. Multiplied by the number of realization the latest price:

[DavidFoster1]

I suspect that is the problem then as you will be changing the range of prices available by making the selection. I would suggest having a proper association between the products and their prices. You can look at INTERVALMATCH as a way of joining tables based on a range. Alternatively do a LOOKUP function to more reliably find the price you are after.

Hope that helps