Complex set analysis using ranges and max dates for each dimension value

[m_baroja]

Hi all!

This is being a very challenging problem what I am facing, hope you can help me.

I'm attaching an example Excel file and a .qvw file with loaded data. There, you can see we have sold article amounts by date and article.

Date1	Article	Amount
01/01/2020	Art1	100
02/01/2020	Art1	200
06/01/2020	Art1	300
07/01/2020	Art1	400
02/01/2020	Art2	500
03/01/2020	Art2	600
09/01/2020	Art2	700
10/01/2020	Art2	800

This is the problem: the end user has the possibility to use a selector and write a offset value for the date -in this example, for simplifying purposes, I'm adding to year's first day so many days as vDayCounter variable says. Then, having this calculated date as reference, I need to get the amount corresponding to the maximum date for each article, being this date lower than the calculated date.

Using this table as example, let's say you set vDayCounter=4. So, our calculated day will be 05/01/2020. Then this means we want this amounts:

Date1	Article	Amount
02/01/2020	Art1	200
03/01/2020	Art2	600

I tried AGGR to obtain dates at article level, combining with Max() function, but it didn't work anyway.

Thanks in advance for your interest!

Miguel

[lironbaram · Accepted solution]

hi 

this should solve your question 

FirstSortedValue({<Date1={"<=$(=date(Max(Date1)+vDayCounter))"}>}  aggr( sum({<Date1={"<=$(=date(Max(Date1)+vDayCounter))"}>}Amount),Date1,Article) ,-Date1)

also attach is your demo model  

[m_baroja]

Very nice. It works!

Thank you!