Hello dear community,
I need to find a solution for the following situation:
table 1:
Product | Sales_Month
P1 | Dec
P1 | Feb
P2 | Jan
P2 | Feb
table 2:
Product | Month | Rep
P1 | Nov | John
P1 | Jan | Ema
P2 | Dec| Louis
P2 | Jan | George
what I want is a visualization that
- when I make a selection in table 1 for Product and Sales month
- it displays from table 2 the Rep who corresponds to the Month that is the largest in table 2, but smaller than the Sales Month
example: selection for P1 and Dec - it displays John because Nov is the largest Month in table 2 that is smaller than Dec
Product | Sales Month | Month | Representative
P1 | Dec | Nov | John
OR
example: selection for P2 and Jan - it displays Louis because Dec is the largest Month in table 2 that is smaller than Jan
Product | Sales Month | Month | Representative
P2 | Jan| Dec| Louis
I tried different aggr formulas in combination to min/max but to no avail.
Your help is much appreciated
thank you
PS obviously the data structure is much more complicated
