Hi everyone,
I don't succeed to combine 2 rolling periods, 1 for sales, 1 as a qualifier for sales.
I have a product table with Category, Product and Product_Sales_Start_Date (format is DD/MM/YYYY)
I have a sales table with Product, Sales_Date (format is DD/MM/YYYY), Amount.
I'm trying to achieve this:
| |
MAT May |
MAT May |
MAT Apr |
MAT Apr |
MAT March |
MAT March |
| Category 1 |
total sales |
sales of products having a Sales Start Date within the last x months |
total sales |
sales of products having a Sales Start Date within the last x months |
total sales |
sales of products having a Sales Start Date within the last x months |
| Category 2 |
total sales |
sales of products having a Sales Start Date within the last x months |
total sales |
sales of products having a Sales Start Date within the last x months |
total sales |
sales of products having a Sales Start Date within the last x months |
the x from above is given by a variable vAge, and the user just has to enter the number of months he wishes to have (he wants to have all products younger than 18 months, so he writes 18 in the input field).
I have a Master Calendar created from the Sales_Date.
I created an As-of table from the Master Calendar, with MonthYear, AsOfMonth and MonthDiff to be able to do the rolling MATs.
I created another As-of table from the Product Sales Start Dates with PrdMonthYear from the Product Sales Start dates, AsOfMonth_Prd and MonthDiff_Prd.
the MAT calculation works (Sum({$<MonthDiff={"<12"}>} Amount)), but not the one filtering on the products younger than x months:
- I tried by entering a fixed number directly in the formula to see if the issue was coming fro the variable, but that doesn't work: Sum({$<MonthDiff={"<12"}, MonthDiff_Prd={"<18"}>} Amount).
AsOfMonth is my column header.
- I tried only Sum({$<MonthDiff_Prd={"<18"}>} Amount) which doesn't work either
I guess the formula with MonthDiff_Prd is not working because there is no link between AsOfMonth and MonthDiff_Prd, but I don't see how to link the both of them, and of course if I don't put AsOfMonth but MonthYear as column header, nothing of the above works.
I also tried with PrdMonthYear as MonthYear, but then it creates a loop, and AsOfMonth_Prd as AsOfMonth but then there is no distinction between MonthDiff and MonthDiff_Prd
thanks a lot for your help!
Delphine
