Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
Creator
Creator

Selecting the maximum that is lower than a filtered value

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

0 Replies