Conditional Formatting Based on Previous Group Value

[Oreynolds]

Hi, I have a year on year report which shows three group values for each year. I would like to use conditional formatting to show if the value for each respective group has increased or decreased from the previous year.

This is what my query data looks like when filtered to two years. So in this case I want to make my 2024 values either RED or GREEN depending on the previous years figure for that group. The groups are: Jobs/FRAs, Reactives, Service.

Can anyone help with how to go about this?

Thanks

 

[CJ_London]

Is that an xtab query? If so, provide the sql

And conditional formatting only works in forms and reports

 

[Pat Hartman]

I don't know if you can use domain functions in conditional formatting but a direct lookup is the only way to find a previous value.

 

[CJ_London]

Not sure either and domain functions are likely to be slow for something like this but to get the previous data could work utilising a left non standard join. But would need to see the sql

 

[arnelgp]

see this demo, open form1 and see the Conditional Format on Month1 and Month2 fields.

 

[DHookom]

Hi, I have a year on year report which shows three group values for each year. I would like to use conditional formatting to show if the value for each respective group has increased or decreased from the previous year.

This is what my query data looks like when filtered to two years. So in this case I want to make my 2024 values either RED or GREEN depending on the previous years figure for that group. The groups are: Jobs/FRAs, Reactives, Service.

View attachment 116705

Can anyone help with how to go about this?

Thanks

You could have pasted the datasheet into Excel and formatted the cells to provide a better specification.

I expect you could get the previous year’s values in the reports recordsource.

 

[Oreynolds]

see this demo, open form1 and see the Conditional Format on Month1 and Month2 fields.

Thanks for this, much appreciated. Your solution does essentially work, however when I build this into my report it takes around 10mins+ to load and format itself! I presume there are no other solutions that would be quicker?

 

[Oreynolds]

You could have pasted the datasheet into Excel and formatted the cells to provide a better specification.

I expect you could get the previous year’s values in the reports recordsource.

This is what the report needs to look like when formatted correctly:

 

[Oreynolds]

Not sure either and domain functions are likely to be slow for something like this but to get the previous data could work utilising a left non standard join. But would need to see the sql

Hi, thanks. This is the SQL of one of the source queries. I have 3 of these that are then brought together in a UNI query.

SELECT IIf(Month([InvoiceDate])>11,Year([InvoiceDate])+1,Year([InvoiceDate])) AS FY, "Jobs/FRAs" AS Type, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=1,CCur(Nz([InvoiceAmount],0)),0)) AS Month1, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=2,CCur(Nz([InvoiceAmount],0)),0)) AS Month2, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=3,CCur(Nz([InvoiceAmount],0)),0)) AS Month3, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=4,CCur(Nz([InvoiceAmount],0)),0)) AS Month4, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=5,CCur(Nz([InvoiceAmount],0)),0)) AS Month5, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=6,CCur(Nz([InvoiceAmount],0)),0)) AS Month6, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=7,CCur(Nz([InvoiceAmount],0)),0)) AS Month7, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=8,CCur(Nz([InvoiceAmount],0)),0)) AS Month8, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=9,CCur(Nz([InvoiceAmount],0)),0)) AS Month9, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=10,CCur(Nz([InvoiceAmount],0)),0)) AS Month10, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=11,CCur(Nz([InvoiceAmount],0)),0)) AS Month11, Sum(IIf(IIf(Month([InvoiceDate])=12,1,Month([InvoiceDate])+1)=12,CCur(Nz([InvoiceAmount],0)),0)) AS Month12, [Month1]+[Month2]+[Month3]+[Month4]+[Month5]+[Month6]+[Month7]+[Month8]+[Month9]+[Month10]+[Month11]+[Month12] AS Total
FROM tblCustomerInvoices
WHERE (((tblCustomerInvoices.InvoiceNumber) Is Not Null) AND ((tblCustomerInvoices.OrderNumber) Like "J*" Or (tblCustomerInvoices.OrderNumber) Like "FRA*") AND ((tblCustomerInvoices.Proforma)=False))
GROUP BY IIf(Month([InvoiceDate])>11,Year([InvoiceDate])+1,Year([InvoiceDate]))
HAVING (((IIf(Month([InvoiceDate])>11,Year([InvoiceDate])+1,Year([InvoiceDate])))>2014));

 

[Oreynolds]

Guys - Thanks for your help. In the end I solved the problem using @arnelgp solution. Whilst the DLookups were slow using the report recordsource I created a temp table and then used that as a recordsource for the report/DLookups and now it loads in a reasonable timeframe. Thanks

 

[arnelgp]

you may try to use Recordset from your Query and maybe eliminate the need for temp table.
see the code on Module1 and the Load event of the report.