Hey, I've been doing my subsummaries out and about recently, and I want to improve my conditional formatting part. The output is very basic - if the value is 50% higher than average, then the text turns green, if the value is 33% lower, then text turns red.

What I want to improve: if the sub-summary period (I have year, half, quarter, month, week, day) hasn't yet ended, currently, despite how good the numbers are, most of the fields are displayed in red, because there are some 5 months till the end of the year. So it's based on average of monthly totals.

I want the conditional formatting conditions to be based on daily average (not average of the period totals) x number of days in the set, so this way even if it's month of January, if numbers are exceeding averages, it would display in green, and not in red.

My way of thinking was to get Min (a_day) and Max (a_day) to figure out the number of days in the selected summary and then multiply it by daily average of that period. Sadly, Min/Max doesn't work in my subsummary. Maybe you know the way I could solve this out?

And what's additional to this - I have two global fields Day From and Day To, by which I filter data, so, if we took yearly example, if Day From is 1st of March and Day To is today, the calculation should use 1st of March as Day From, and not 1st of January, which is 1st day of the selected period, in this case, a year.

So, you want to take a Summary field's data and if the period which covers the summary, say a month, is not yet complete, get a pro-rated total?

To get the number of days as a fraction of the month's total, you could try:

Let ( [

dt = table::a_day ;

today = Get ( CurrentDate ) ;

first.day = Date ( Month ( dt ) ; 1 ; Year ( dt ) ) ;

last.day = Date ( Month ( dt ) + 1 ; 0 ; Year ( dt ) ) ;

//If the month is finished, use 1. If it's not, get the fraction of the month completed

fraction = If ( last.day ≤ today ; 1 ; Day ( today ) / ( last.day - first.day ) ) ;

];

fraction

)