Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table (DATE_CON has format: DD/MM/YYYY):
MALL | UNIT | BRAND | DATE_CON | CONTRACT |
---|---|---|---|---|
MALL1 | UNIT01 | BRAND1 | 01/04/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/05/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/06/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/07/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/08/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/09/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/10/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/11/2017 | 35 |
MALL1 | UNIT01 | BRAND1 | 01/12/2017 | 35 |
MALL1 | UNIT11 | BRAND1 | 01/12/2017 | 90 |
MALL2 | UNIT02 | BRAND1 | 01/06/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/07/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/08/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/09/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/10/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/11/2017 | 33 |
MALL2 | UNIT02 | BRAND1 | 01/12/2017 | 33 |
MALL3 | UNIT03 | BRAND1 | 01/03/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/04/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/05/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/06/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/07/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/08/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/09/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/10/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/11/2017 | 42 |
MALL3 | UNIT03 | BRAND1 | 01/12/2017 | 42 |
MALL3 | UNIT33 | BRAND1 | 01/12/2017 | 48 |
MALL4 | UNIT04 | BRAND1 | 01/02/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/03/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/04/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/05/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/06/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/07/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/08/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/09/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/10/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/11/2017 | 77 |
MALL4 | UNIT04 | BRAND1 | 01/12/2017 | 77 |
MALL5 | UNIT05 | BRAND1 | 01/02/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/03/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/04/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/05/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/06/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/07/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/08/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/09/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/10/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/11/2017 | 65 |
MALL5 | UNIT05 | BRAND1 | 01/12/2017 | 65 |
MALL6 | UNIT06 | BRAND1 | 01/04/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/05/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/06/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/07/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/08/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/09/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/10/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/11/2017 | 14 |
MALL6 | UNIT06 | BRAND1 | 01/12/2017 | 14 |
MALL7 | UNIT07 | BRAND1 | 01/07/2017 | 39 |
MALL7 | UNIT07 | BRAND1 | 01/08/2017 | 39 |
MALL7 | UNIT07 | BRAND1 | 01/09/2017 | 39 |
MALL7 | UNIT07 | BRAND1 | 01/10/2017 | 39 |
MALL7 | UNIT07 | BRAND1 | 01/11/2017 | 39 |
MALL7 | UNIT07 | BRAND1 | 01/12/2017 | 39 |
MALL8 | UNIT08 | BRAND1 | 01/08/2017 | 52 |
MALL8 | UNIT08 | BRAND1 | 01/09/2017 | 52 |
MALL8 | UNIT08 | BRAND1 | 01/10/2017 | 52 |
MALL8 | UNIT08 | BRAND1 | 01/11/2017 | 52 |
MALL8 | UNIT08 | BRAND1 | 01/12/2017 | 52 |
MALL9 | UNIT09 | BRAND1 | 01/04/2015 | 87 |
MALL9 | UNIT09 | BRAND1 | 01/05/2015 | 87 |
MALL9 | UNIT09 | BRAND1 | 01/06/2015 | 87 |
MALL9 | UNIT09 | BRAND1 | 01/07/2015 | 87 |
MALL9 | UNIT09 | BRAND1 | 01/08/2015 | 87 |
MALL9 | UNIT09 | BRAND1 | 01/09/2015 | 87 |
MALL10 | UNIT10 | BRAND1 | 01/12/2010 | 25 |
MALL10 | UNIT10 | BRAND1 | 01/01/2011 | 25 |
MALL10 | UNIT10 | BRAND1 | 01/02/2011 | 25 |
MALL10 | UNIT10 | BRAND1 | 01/03/2011 | 25 |
MALL10 | UNIT10 | BRAND1 | 01/04/2011 | 25 |
MALL10 | UNIT10 | BRAND1 | 01/05/2011 | 25 |
What I need is to get the last Contract (getting the Max Date) for every Mall and Unit.
Note that there could be more than 1 Unit for a Mall for a Max Date, like Mall 1 or Mall 3.
I should get the following:
MALL | UNIT | BRAND | DATE_CON | CONTRACT |
---|---|---|---|---|
MALL1 | UNIT01 | BRAND1 | 01/12/2017 | 35 |
MALL1 | UNIT11 | BRAND1 | 01/12/2017 | 90 |
MALL2 | UNIT02 | BRAND1 | 01/12/2017 | 33 |
MALL3 | UNIT03 | BRAND1 | 01/12/2017 | 42 |
MALL3 | UNIT33 | BRAND1 | 01/12/2017 | 48 |
MALL4 | UNIT04 | BRAND1 | 01/12/2017 | 77 |
MALL5 | UNIT05 | BRAND1 | 01/12/2017 | 65 |
MALL6 | UNIT06 | BRAND1 | 01/12/2017 | 14 |
MALL7 | UNIT07 | BRAND1 | 01/12/2017 | 39 |
MALL8 | UNIT08 | BRAND1 | 01/12/2017 | 52 |
MALL9 | UNIT09 | BRAND1 | 01/09/2015 | 87 |
MALL10 | UNIT10 | BRAND1 | 01/05/2011 | 25 |
Do you know how could I do that? I'd need to do this as an expression for a Pivot Table.
Thank you!!!
Hi,
just use Date(Max(date_field))
see attached
Just use this expression in pivot table
Sum({<DATE_CON = {'$(=Date(Max(DATE_CON)))'}>} CONTRACT)
Exp1=Date(Max(DATE_CON))
Exp2=FirstSortedValue(CONTRACT, -DATE_CON)
Hello Anil,
I've tried this, but the problem is that it doesn't shows Malls 9 and 10 in the table.
I believe that happens because Malls 1 to 8 have DATE_CON = 01/12/2017 and Mall 9 has 01/09/2015 and Mall 10 has 01/05/2011.
Do you know how could I fix it?
Thanks!
Hello Tresesco,
Is there any way to use a single expression instead of two? Because I wouldn't really need to show the date.
Thanks!
Just omit the first expression. Second expression is not at all dependent on first, because it's not a dimension.