Hi
I am trying to create a table which shows 4 columns by MonthYear (the dimension):
- Start value
- New Customers for the month
- Customers removed last month
- Movement in Other Customers
- End Value
I am trying to do this using set analysis, however only the end value and New entries is simple for me.
for example I've tried the following to get the Star value (which qould be equal to month end value of last month):
Sum({<
MonthYear={"$(=MonthEnd(AddMonths((MonthYear), -1)))"}>}
[Inventory])
I am not an IT person, but a business user trying to figure this out.
I've managed the New entries by creating a flag in my script if it's the 1st date a record appears:
Sum({1<
Flag_First_Date = {1}
>}
[Inventory])
Similarly I've created a flag for the last time an entry appears, but this is shown in the previous month.
Example data:
MonthYear InventoryBalance Customer Flag_First_Date Flag_Last_Date
03/2015 250 b 1 0
04/2015 100 a 1 0
04/2015 50 b 0 1
04/2015 80 c 1 0
05/2015 110 a 0 0
05/2015 130 c 0 1
06/2015 150 a 0 1
Output I'm hoping to see:
MonthYear Start_Value New Removed Other_Movements End_Value
04/2015 250 180 0 -200 230
05/2015 230 0 -50 60 240
06/2015 240 0 -130 40 150
Hoping this makes sense and someone can assist?
Thanks
