Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I'm trying to make a chart:
- dimention is MonthYear
- expression is Sum(Sales) for the Customers with RegistrationDate < start of current MonthYear in a dimention (they have registered before start of current month)
I have tryed
sum({<[CustomerID]={"=([CustomerRegistrationDate]<MonthStart(Min(MonthYear)))"}>} Sales)
but it filters out all Customers who have registered before the first one month in a chart, so it's not what I need if there are more than one month in a chart.
So I need to refer to a current value of dimention someway instead of min().
Any help is appreciated.
When you use set analysis the set is calculated at the chart level, not at the row level. So your set analysis expression won't work since you're using MonthYear as a dimension. I think you'll need to add an extra table in the script that links each MonthYear value with all the CustomerRegistrationDates that lie before that MonthYear value.
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
When you use set analysis the set is calculated at the chart level, not at the row level. So your set analysis expression won't work since you're using MonthYear as a dimension. I think you'll need to add an extra table in the script that links each MonthYear value with all the CustomerRegistrationDates that lie before that MonthYear value.
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
Thank you, Gysbert, for the explanation of fundamentals of set calculation and the detailed issue about rolling totals.
From this i've got an idea to use already linked fields to filter data and it works:
sum({<[OrderID]={"=(MonthStart(OrderDate)>MonthStart([CustomerRegistrationDate]))"}>} Sales)
Instead of filtering out Customers I filter out Orders placed in the month of registration.