dates duplicating data

[#1]

Hey guys!

I have a problem with stock numbers anda different dates

Table 0

PRODUCT

Table 1

PRODUCT - COMPANY - STOCK

Table 2

PRODUCT - COMPANY - DATE - SALES

The problem is that in one pivot table i want to show all products and current stock, but when i do, everything duplicates because of sales dates

How can i "ignore" the dates?

Thanks!

[effinty2112]

Hi Victor,

Does Table1 have one line per product? Maybe a flaw in your script has caused replications of lines in your data model. If there is only one line per product in this table then a straight table with dimension PRODUCT should work.

If Table 1 has stock qtys at a certain date or time do you want a calculated stock based on these figures minus sales since date of stock check? If so we may need to consider goods coming into stock since the stock was counted.

Regards

Andrew

[#3]

Hey Andrew

using real name for table

product_table
store_table
stock_table (with only product, store, stock) (no date, only the current stock)
sales table (with product, store, date, sale)

if i load only one day, it has the number i need, if i load more days, it duplicate

and if i filter dates, it will filter products, and i dont want that

I need to go to the report without selecting dates, and have a number with no duplicationg because of the dates

[effinty2112]

Hi Victor,

Is the problem you're having to do with showing sales by date and stock in a single straight table?

if you make a straight table like:

Product	Store	Date	Sum(Sale)
A	Renton	01/05/2017	45
A	Alexandria	01/05/2017	12
A	Renton	30/04/2017	36
A	Alexandria	01/05/2017	25
A	Renton	02/05/2017	15

then add an expression Sum(Stock) you'll get something like

Product	Store	Date	Sum(Sale)	Sum(Stock)
A	Renton	01/05/2017	45	100
A	Alexandria	01/05/2017	12	60
A	Renton	30/04/2017	36	100
A	Alexandria	01/05/2017	25	60
A	Renton	02/05/2017	15	100

Because of your data model values of Stock will be associated to more than one date value if the product is sold on multiple dates.

Is this the problem?

Cheers

Andrew