Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I have products with a start date and a finish date:
Product Date
1 1/1/2020
1 1/1/2030
2 2/1/2020
2 2/2/2030
And I want to create a graph or chart that sums the amount of products "available" over the time period of first product start date to final product finish date. For example in 1/5/2020 there would be 1, in 2/5/2020 there would be 2 and in 1/5/2030 there would be 1 again. I can create another column that says finish instead of an extra row but regardless please help!
May be this
Sum({<Date = {"<= max(Date)"}>} Product)
Sum({<Date = {">= min(StartDate)<= max(FinishDate)"}>} Product)
It's very hard to impossible to create working views with such a data-structure. Therefore I suggest to resolve the date-area to dedicated dates which are then associated to a calendar. In your case you may try something like this:
load Product, date(From + iterno() -1) as DateX
while From + iterno() -1 <= To;
load Product, rangemin(Date, today()) as To, previous(Date) as From
resident Source order by Product, Date desc;