set expression with expression or dimensions

[#1]

Hi,
I've the following problem.
I want to see the stock day by day of my products, but i do not have historical data.
I've a table where I've the current stock and another table with the sellings.
The selling table have 2 fields StockFrom and SellDay.
StockFrom is even the key field to connecto the selling table with the current stock.

So now I need to find out the stock at a give day;
The formula for every day is
Current stock with StockFrom<Min([given date])
+ Current Stock with StockFrom=[given date]
+ selling with StockFrom=[given date]
- selling with SellDay=[given date].

Now.. everything quite easy if you use a Data Island and IFs... but extremly slow in my situation, so I decided to use StockFrom as main date.
so... evrything easy... until the SellDay = [given date]
I was thinking to use something like

Count(

     {<SellDay={StockFrom}>}

TOTAL SellDay)

but I can't handle it...
Does someone have an idea of how could it be done?

Thank you

[Miguel_Angel_Baeyens]

Hi,

Did you try using P() function in set analysis to get all possible values of StockFrom into SellDay?

Count(
     {< SellDay = P(StockFrom) >}
TOTAL SellDay)

Rather than playing with If() and data islands, I'd let the users populate the date using a slider/calendar object and a variable, like in this example.

Hope that helps.

Miguel

[#3]

yeas I tried, but in that way i have for every record the sum of the All SellDay.

ie:
StockFrom = '01/01/2012' [...] '10/01/2012'
At the DimensionValue '02/01/2012' I would like Only the sell of the '02/01/2012', including the SellDay = P(StockFrom) consider mi all the SellDay in the period and the values is the same for every day

I was thinking to join it with StockFrom something else.
Anyway, soon I'll get a look to your example and I'll let you know.
I supposed it should be something like if want to see something like
"complete date with year" - Products sold in date - Product sold in the same date of the previous year

but i'm getting lost in it

Anyway, now i'll take you file, if i'll have some problems i'll upload mine
k you

than

[#4]

Here the example file, hope it has data inside

[Miguel_Angel_Baeyens]

Hi Terry,

Remove the TOTAL from the Count() expression in the chart on the right. As specified, TOTAL will return the same values for all the values in the dimension. Are those the right values without the TOTAL? In the script, there are only two autogenerated tables with dates, but not the rest of the data.

Miguel

[#6]

the rest of the data i removed since it loads from SQL DataBase and there were account and passwords in

Removing the TOTAL (there is not in the sent file) is just taking some results, and is giving me the values of Sold elements with StockFrom = the dimension date (i want where SellDay = dimension date)

the queries are

SQL SELECT SellDay,  StockFrom FROM Contracts

and

SQL SELECT StockFrom, VIN FROM Stock

[#7 · Accepted solution]

I think maybe I should use a support table something like

Select SellDay AS StockFrom, ContractID FROM Contract

And take the Count(ContractID)

what you think?