Expression for 'Closing' Figures

[johnso2080]

Hi, I'm trying to create an expression that gives me the sum of stock from only the LAST date of the current selection...

A basic equivalent of the tables used are...

StockHistory:

Product ID

Date

Stock Level

Products:

Product ID

Category 1

Category 2

Category 3

Calendar:

Date

Week

Month

I need, for any selections that may occur, the SUM([Stock Level]) only for the last date in the selection or the dimension in a chart. For example we might want to see a table with dimensions [Week] and [Category 3] and we would want to see the sum of stock for each category on the last day of each week shown in the table.

Also, the dimensions are not set in the tables/charts themselves, they are dynamic based on user selections/variables.

Hope that's clear! Any help much appreciated!

Darren

[mato32188]

Hi Darren,

try to use Set Analysis in your Expression (sum {<Date = {'$(=max(Date))'}>} StockLevel)

BR

M

[johnso2080]

Thanks.

This expression only gives the closing stock for the very last date in the dataset. When used in a straight table broken down by week for example, it only shows the last week.

[mato32188]

Ok,  what about firstsortedvalue(stocklevel, - date)

[johnso2080]

Hello,

This brings back just one stock figure for one item (the last one). I need the sum of all items on the last day of the selection or dimension.

Thanks for your help

Darren

[#6]

Hi Darren,

I would look into creating some fields with the relevant point in time flags you require for your dataset.

Sounds like you need an [Is Week End] and [Is Month End] flag. Once you have those flags you can use them within you set analysis expression to only give you the sum of the required days.

Sum( {<[Is Week End]= {1}>} StockLevel)

would the only bring back data for the required days meaning you would get the correct figure in each of your week dimension values. Same applies for month

hope that helps

Joe

[mato32188]

.

[johnso2080]

Thanks Joe,

I think this would be a bit awkward to implement, the user will use a dimension 'group' to choose how they trend the data (by date, week, month etc) so I won't know what the user is looking at to use the relevant parameter. I was ideally looking for one expression that works holistically. So far I've got to this...

FirstSortedValue(AGGR(SUM({$} [StockLevel]),[Date]),0-AGGR(MAX({$} [Date]),[Date]))

Which seems to work when breaking down the total stock by period, however, when add another dimension such as product category, the data is completely wrong.

Thanks for your help

Darren

[#9]

Totally get where you are coming from, rather than a single complex expression, I would maybe look to use multiple simple expressions which are conditionally calculated.

You can use GetCurrentField(groupname) to give you which dimension is currently active in the group and based on that conditionally show the required expression.

condition: GetCurrentField(groupname)='Week'

expression: Sum( {<[Is Week End]= {1}>} StockLevel)

condition: GetCurrentField(groupname)='Month'

expression: Sum( {<[Is Month End]= {1}>} StockLevel)

I think you will find performance better also

hope that helps

Joe

[johnso2080]

Thanks Martin,

I'm afraid I haven't been able to apply this expression to my data model.

I've also found when I extend the data in your example (attached) I don't get results? Any ideas?

Thanks again

Darren