Forum Discussion
How to filter data for rolling 24 months using SAP BW BEx Query
Hello Everyone,
I have to pass two required parameters StartDate and EndDate at the BEx query level. If I pass Start Date as "20220101" and EndDate as "20220131", these values are fixed in the parameters section. What I'm looking at here is how can I achieve a rolling 24 months of data based on the current date when data flow is refreshed daily. What changes do I have to make in the advanced editor for making this query to bring back rolling 24 months of data from the source system?
For Example: If the data flow is refreshed today (08/23/2022), It has to bring all the data from 08/23/2020 - 08/23/2022.
Any suggestions are much appreciated.
4 Replies
praveenk , Use these two dates
Let
StartDate =Date.AddMonths(DateTime.Date( DateTime.FixedLocalNow()),-24),
EndDate= DateTime.Date( DateTime.FixedLocalNow()) ,
<Use these two in you code now>
I don't see the data beyond the parameter's value after adding those two lines to the code. Do I have to remove the date values and substitute them with the StartDate and EndDate?
praveenk , Those two date values you try to use these parameters
if needed convert to text
Date.ToText([StartDate],"yyyyMMdd")
replace 20220101 and the other date and check if it works
- I didn't actually get what you are trying to say. Below is my code, where do you want me to change it?letSource = SapBusinessWarehouse.Cubes("XXX.XXX.", "11", "800", [Implementation = "2.0"]),#"Navigation 1" = Source{[Name = "ZCP_BLUE_Q"]}[Data],Shipping_Logistics_Blue_Box = #"Navigation 1"{[Id = "ZCP_BLUE_Q/ZCP_BLUEBOX_LOGISTICS_Q001"]}[Data],StartDate = Date.AddMonths(DateTime,Date(DateTime.FixedLocalNow()),-24),EndDate = DateTime.Date(DateTime,FixedLocalNow()),#"Added items" = Cube.Transform(Shipping_Logistics_Blue_Box, {{Cube.ApplyParameter, "[!V000001]", {"20220101", "20220131"}}, {Cube.AddMeasureColumn, "Actual Delivery Qty", "[Measures].[00O2TH886DUSLQ0ERABGKVORJ]"}, {Cube.AddMeasureColumn, "Delivery Quantity", "[Measures].[00O2TH886DUSLQ0ERABGKWE1R]"}, {Cube.AddAndExpandDimensionColumn, "[0COSTELMNT]", {"[0COSTELMNT].[LEVEL01]"}, {"Cost Element Level 01"}}, {Cube.AddAndExpandDimensionColumn, "[HPPTASTRD]", {"[HPPTASTRD].[LEVEL01]"}, {"Transaction Date Level 01"}}})in#"Added items"
