
1. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation
Shinichiro Murakami Jun 22, 2018 4:06 PM (in response to Lindon Belshe)Hi Lindon
I am not sure what kind of aggregation you want to get from the sample file.
Anyways, hope this helps.
Thanks,
Shin

Example_SM_10.5.twbx 495.4 KB


2. Re: Conditional Formatting based on Results of a Diff Quick Table Calculation
Lindon Belshe Jul 10, 2018 12:53 PM (in response to Shinichiro Murakami)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
Shinichiro Murakami Jul 10, 2018 3:57 PM (in response to Lindon Belshe)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