topic Re: How to separate one day of data from four days of data? in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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.

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 — Wed, 31 May 2017 08:25:07 GMT

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 — Wed, 31 May 2017 08:45:14 GMT

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')