Inventory Calculation with dynamic calculated dimension

[#1]

Hi,

I would like to achieve the following:

Get the booked Stock on hand for the last 'vLookback_weeks' weeks from 'reference date'.

Where:

1. Reference date - End of current week (default) or End of selected week. For example - If no selections were made - 20/11. If week 44 was selected, return 6/11

2. vLookback_weeks - number of weeks to loop back. stored in variable to be manipulated by slider object

3. Stock on hand (Expression)- accumulate all 'stock' that occurs between 'Date From' to 'Date To'

sum(DISTINCT
                    if(
                              min(Date) >= [Date From] and 
                        min(Date) < [Date To]
                        ,
                       Stock,0
                    )
          )

4 Weeks (Calculated dimension) - show all weeks that occur between 'reference date' and (''reference date'' - X weeks).

=IF(Date >=Weekstart(Date(Rangemin(WeekEnd(aggr(Max(TOTAL [Date]),[DI WeekYear])), WeekEnd(Today()))),($(vLookback_weeks) * -1)) and 
    Date < Date(Rangemin(WeekEnd(Max(TOTAL [Date])), WeekEnd(Today()))), 
[DI WeekYear]

Please note that the calendar table is a Date island table. I'm using this approach to trap inventory transactions which have a span of more than a week.

The problems i'm facing with are:

1. It does not work...

2. Using date island approach has a major impact on performance

Please have a look at the attached file

Best regards,

Dror

o

[swuehl]

Hi Dror,

please find attached a start. I don't think that this handles all of your requirements correct, but let's do one step after the other.

I think in your sample, there was a selection on one Date value, which in case this really was should be avoided, since you want this date to draw against without any range limitations.

In your expression, I changed min(Date) to Date. You can't use an aggregation function like min() within another aggregation function like sum() without advanced aggregation (using aggr() function). I think you don't need min function here, so I just removed it (but am not 100% sure).

You now get results for the accumulated stock, I checked with some data and it looks reasonably to me. If you want to accumulate all values per Date, you might need to remove the DISTINCT from the sum and you might need to think about if same stock values in different periods could happen (thus they maybe won't be considered correctly using DISTINCT).

Also your dimension Week range is sensitive to your slider, but not sure if this is fully according to your requirements.

Just have a look and specify what is not correct.

Regards,

Stefan

[#3]

Stefan,

Thanks! So far it looks good. I'll jump into it and let you know

Regards,

Dror