I am trying to calculate the rolling values for n period.
I am using below code to calculate rolling but is there any way to do only rolling 3 or 4 or 5 months?
if(Product=previous(Product), rangesum(Amount, peek(Rolling)),Amount) as Rolling
Attached excel sheet is the expected output
Table2: LOAD * INLINE [ Date, Product, Amount 01-01-2019, A, 5 01-02-2019, A, 10 01-03-2019, A, 15 01-04-2019, A, 20 01-05-2019, A, 25 01-06-2019, A, 30 01-01-2019, B, 1 01-02-2019, B, 2 01-03-2019, B, 3 01-04-2019, B, 4 01-05-2019, B, 5 01-06-2019, B, 6 ]; Left Join (Table2) LOAD * INLINE [ Product, Rolling A, 2 B, 3 ]; Left Join (Table2) LOAD Date as AsOfDate, Date(Date + IterNo() - 1) as Date, Product, IterNo() - 1 as Count Resident Table2 While IterNo() <= Rolling; FinalTable: LOAD AsOfDate as Date, Product, Only(If(AsOfDate = Date, Amount)) as Amount, Sum(Amount) as RollingAmount Resident Table2 Group By AsOfDate, Product; DROP Table Table2;
Sales: LOAD Product, Period, Amount, Output FROM Cumalative.xlsx (ooxml, embedded labels); Sales_1: LOAD Product, Period, Amount, RangeSum(If(Product = Previous(Previous(Product)), Previous(Previous(Amount))),
If(Product = Previous(Product), Previous(Amount)),
Amount) as Rolling Resident Sales Order by Product, Period desc; DROP Table Sales;
In that case, I would say you can use The As Of Table... this can give you a partial script solution... or you can left join the As Of Table into your main table and then use Group by to do what you want. Regardless of what you do... it might all be a little tedious.... I guess you are in the wrong profession if you are afraid of writing a little tedious script ;).
I wanted to make it dynamic. For instance, if user wants 3months rolling then i will calculate for 3 months, if user wants 5months then i will use for 5months.
in the front end ,i can use this formula. So here 3 can be made as variable and by changing this variable,i can calculate accordingly.
But how do we achieve the same in load script?
In the front end, i am able to achieve by defining "vRolling" as variable and use the same in the chart expression.
Need to achieve the same in load script.
Firstly I want to say that it's better to solve it in front-end like what you do, create a variable and input box.
Because even achieve this in back-end, the variable will be useless, because even user change it, you have calculate it on back-end, the value won't be change. So I don't know why you want to do it in back-end.
But anyway, it really takes me a lot of hours to try to solve that. I created a variable in the back-end, if you want to change to 5/7 month, you need to change the variable, but user can not do that I think.
Or what you want is even user change the variable in front-end, the value will be change? I think it's not possible.
So, once the variable is changed, your user will trigger a reload? Is this app sitting on QMC or is this for a user locally? I am just trying to understand the reason behind making this difficult by doing it in the script and asking the user to reload if this can be done with RangeSum(Above()) or using The As Of Table. Have you read about The As Of Table yet? May be if you have not, this might be the best solution for you?
It will be done locally.Yes i did read about 'as of' table. It will solve my purpose partly.
How about the one below. I load two tables Table 1 and Table 2.
Table 2 will have column called ROLLING, which will calculate based on the values defined in Table 1?
Is this possible?
I am not doing this in the front end because this is not the end state. Based on the above table , in the back end, I will be using calculated field for other calculations and then storing the table in csv using Store command.