4 Replies Latest reply on Jul 10, 2018 11:00 PM by Shinichiro Murakami

# Conditional Formatting based on Results of a Diff Quick Table Calculation

Hi There,

I have a sample worksheet attached. Essentially, I have written an LOD function to get the percentage of each type by group and date (aggregated to the monthly level). My question, which has some broad applications, is this: how can I most easily now have the ability to color based on the difference (percentage) between the two most recent months of the data (essentially what is shown on the attached file) - meaning if the two most recent months (in the data, not by calendar) are trending upwards, I want to apply a green color to other worksheets. I know the answer is going to be a calculated field building upon the LOD function that I can then through into the color marks tool when need, but any guidance on what that formula looks like would be great!

Thanks!

• ###### 1. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation

Hi Lindon

I am not sure what kind of aggregation you  want to get from the sample file.

Anyways, hope this helps.

Thanks,

Shin

• ###### 2. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation

Hi Shinichiro. This is helpful and almost gets at the request. Basically, I needed to get a calculated field that I can apply to other worksheets.

I've started this by adjusting the RECENT MONTH functions to be True or False and relative to the date field...

RECENT MONTH

IF [Day]<=({FIXED: max(DATETRUNC('month',[Day]))}) THEN "True" ELSE "False" END

RECENT MONTH -1

IF [Day]>=({FIXED: MAX(IF [Day] < ({FIXED: max(DATETRUNC('month',[Day]))}) THEN DATETRUNC('month', [Day]) END)}) THEN "True" ELSE "False" END

Then I added a percentage change calculation, which sums the number of records...

IF

(SUM(IF[RECENT MONTH]="True" THEN [Number of Records] END)-SUM(IF[RECENT MONTH -1]="True" THEN [Number of Records] END))/SUM(IF[RECENT MONTH -1]="True" THEN [Number of Records] END) >0.05

THEN "INCREASED" ELSEIF

(SUM(IF[RECENT MONTH]="True" THEN [Number of Records] END)-SUM(IF[RECENT MONTH -1]="True" THEN [Number of Records] END))/SUM(IF[RECENT MONTH -1]="True" THEN [Number of Records] END) <= -0.05

THEN "DECREASED" ELSE "NEUTRAL" END

It works if no filters are added. The problem I am still running into is that if there is a date filter on a worksheet (say to only view the number of records for February 2017, it filters the rows and I can't use this conditional formatting. I guess what would help is if there is a way to apply a filter to some fields but not affect others in the same worksheet.

• ###### 3. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation

HI Lindon,

Don't remember the requirement very well but in general,

In case applying filter makes some troubles, try change the filter to context.

I will revisit the thread when i have time.

Thanks,

Shin

• ###### 4. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation

Not quite sure but probably this?

Hide "False"