Dear Manish, I apologize for long lapse of time until I formalize the feedback. At the same time, I deeply appreicate for your fast response.
Would you possibly advise how we could construct logic in line with below:
- Reporting month is defined as month(Today(0) - $(Y)), where Y is integer, which user input for month-shift.
- Sum weight, which belongs in the reporting month or before, only for those Maximum Date is in the current reporting month. This is Max(TOTAL <ProductSerial> if(DocDate<=MonthEnd(Today(0),-$(Y)),DocDate)).
- Maximum Date is the date, which is largest until the reporting month. In the source data, there might be date in April, but since reporting month is March, the max date within March is maximum date.
I have prepared a sample QV as attached.
You see the input field, where you may input 1, 2 ..... This will shift the reporting month.
I'd like to produce such:
Sum(if(
InMonthToDate(
Max(TOTAL <ProductSerial> if(DocDate<=MonthEnd(Today(0),-$(Y)),DocDate)),
MonthEnd(Today(0),-$(Y)),0),
//if maximum date resides within the reporting month;
Weight))
//sum all the weight until the reporting month <= I am not certain if this works
Above does not work....I appreciate for your feedback if any.
