Highlight schools that are in a specific date range

[grecon]

Hi I have a cross-tab comparing 2018 and 2019 sales, I would like to highlight the schools that are new for 2019 but haven't been able to figure out how. I tried right clicking on the school and going to format field, border, background and entered:

if{CUST.FIRST_DATE} in Date (2019, 01, 01) to Date (2019, 12, 31)then crYellow

but that doesn't work. Any idea how I can do this? Thanks

 

[kray4660]

I do not know much about crosstabs, but I was able to get a yellow highlight on cells using the following code if year({CUST.FIRST_DATE})= 2019 then cryellow .But I do not know your data, so I do not know if it will work properly.

 

[lbass]

First, insert a group on {table.school} in your main report, and then suppress the groups and detail if you only want to show the crosstab.

Then create a formula like this:

//{@school}:
if year(Minimum({Cust.First_Date},{table.school}))>2018 then
{table.school}+"^" else
{table.school}

Add this as your row field in the crosstab. Then right click on the school name in the crosstab ->format field->background color->x+2 and enter:

if "^" in gridrowcolumnvalue("@school") then //notice that the brackets have been replaced with quotes
cryellow else
crnocolor

Finally, again select the school name in the crosstab->format field->display string->x+2:

if '^' in gridrowcolumnvalue("@school") then
left(gridrowcolumnvalue("@school"), len(gridrowcolumnvalue("@school"))-1) else
gridrowcolumnvalue("@school")

-LB