Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I am trying to create a set analysis which is not giving me any results, and I am not too sure why.
I have two tables
STOCKS with Dates, Quantity, ProductID....
CALENDAR with Date, Year, Month....
I would like to sum all the Stock Quantities for a Date that is the greatest less than another Date x (in my case, the end of the month). In other words, get the max date which has data and is less than the end of the month, and sum all the stock Quantity on that day.
To do that I have done the following:
vMonthLastDay = MonthEnd(Date)
vMonthMaxDate = date(Max({< Date= {"=Date< '$(vMonthLastDay)'"}, Quantity= {"> 0"}>} Date))
Here, vMonthMaxDate does give me the date I desire, and seems to work perfectly.
However, when I use it in the following:
vQuantityMonth = sum({<Date= {'$(vMonthMaxDate )'} >} Quantity)
I get no results. Could anyone point out where am I failing? why doesnt it sum the Quantity when I am providing the rigth date and there is data?
Thank you,
Federico Arribas
Try below
vMonthMaxDate
= date(Max({< Date= {"< $(vMonthLastDay)"}, Quantity= {"> 0"}>} Date))
QuantityMonth
= sum({<Date= {"$(vMonthMaxDate )"} >} Quantity)