Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

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

Hi All,

I have a Straight table like below:

Screenshot_10.png

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 NameDateAverage
Bala_AMI04/09/2017PM0.25
Cheta_AMI04/09/2017PM1.14
Metro_AMI04/09/2017PM1

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

1 Solution

Accepted Solutions
Sergey_Shuklin
Valued Contributor

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

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

)

View solution in original post

2 Replies
Sergey_Shuklin
Valued Contributor

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

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

)

View solution in original post

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

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