How to separate one day of data from four days of data?

[#1]

Hi All,

I have a Straight table like below:

The formula to get Average:

Today-Previous day/Previous day

Based on the above formula I have used an expression to get Average:

=Num((Sum(TGB_Counts)-above(TOTAL sum( TGB_Counts ) ))/above(TOTAL sum( TGB_Counts ) ),'#,##0.00')

from above table, i want to separate positive average value for the one date (04/09/2017) like below straight table.

TGB Name	Date	Average
Bala_AMI	04/09/2017PM	0.25
Cheta_AMI	04/09/2017PM	1.14
Metro_AMI	04/09/2017PM	1

Note: I have already separated Postive and Negative Values but I can't able to separate for  only one date (04/09/2017) like above

Please find the attached QVW and Data(Excel sheet)

Please Help me Out of this

Thanks,

Muthukumar

[Sergey_Shuklin · Accepted solution]

Hello, Muthu!

In that case the IF condition will be suitable:

if(Date = '04/09/2017 PM',

(Sum(TGB_Counts)-above(TOTAL sum(TGB_Counts )))/above(TOTAL sum(TGB_Counts))

)

[sunny_talwar]

Try this

=Num(RangeMax((Sum(TGB_Counts)-above(TOTAL sum( TGB_Counts ) ))/above(TOTAL sum( TGB_Counts ) ), 0) *

Avg({<Date = {"=Date#(SubField(Date, ' ', 1), 'MM/DD/YYYY') = Max(TOTAL Date#(SubField(Date, ' ', 1), 'MM/DD/YYYY'))"}>} 1),'#,##0.00')