How to get positive values and negative values separated?

[#1]

Hi All,

The below table box is the output I am getting

The average value is the output based on date and value

Name	Date	Value	Avg.Value.
A	1/1/2017PM	4	0
B	1/2/2017PM	7	0.75
C	1/3/2017PM	4	-0.42
D	1/4/2017PM	3	-0.25

The expression to get average value based on below formula:

Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00')

According to the Formula.

(Today-Previous day)/Previous day)*100)/Previous day)*100)/Previous day)*100)/Previous day)*100)/Previous day)*100)/Previous day)*100

Name B:

Today=1/2/2017                value:7:7

Previous day=1/1/2017      value:  4

Formula:

(7-4)/4=0.75

Name C:

Today=1/3/2017                value:4:4:4

Previous day=1/2/2017      value:  7

Formula:

(4-7)/7=-0.42

NameD:

Today=1/4/2017                value:3:3:3

Previous day=1/3/2017      value:4:4:4

Formula:

(3-4)/4=-0.25

so I need a separate table box for positive values and separate table box for negative values based on above expresson

pleasehelp me out of this

Thanks,

Muthu

[Digvijay_Singh]

Can you share expected output, and from table box do you mean straight table? so you want one straight table with positive values and another one with negative values?, more info will help. Thank

[#3]

Thanks for your Response Vijay,

Yes I want separate Straight table for Positive values and separate Straight table for Negative values

i want the output like

Positive Straight table :

Name	Date	Value	Avg.Value.
A	1/1/2017PM	4	0
B	1/2/2017PM	7	0.75

Negative Straight table:

Name	Date	Value	Avg.Value.
C	1/3/2017PM	4	-0.42
D	1/4/2017PM	3	-0.25

Thanks,

Muthu

[Sergey_Shuklin]

Hello, Mathu!

Why you don't wonna use IF condition?

If(formula_result>=0,formula result) for posivite table, and <0 for negative table.

[satishkurra]

‌for positive values use range Max function as

rangemax(yourexpression,0)

for negative values use range min function as

rangemin(yourexpression,0)

[#6]

Thanks for your response Shuklin.

can you please implement it for below Expression expression. because when I implement. It is not working.Can you please help me

The expression to get average value based on below formula:

Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00')

Thanks,

Muthu

[Sergey_Shuklin · Accepted solution]

This:

if(

(Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) )>=0,

(Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ))

is not working?

[SunilChauhan]

for Negative values

If(Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00')<0,Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00'))

for positive values

If(Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00')>=0,Num((Sum(Rolling_7_days.Counts)-above(TOTAL sum( Rolling_7_days.Counts ) ))/above(TOTAL sum( Rolling_7_days.Counts ) ),'#,##0.00'))

[#9]

Yes it is not working.

based on my requirement my question is wrong

But the answer what you said is correct

thanks for your Expression now i will close this discussion by marking your answer as correct will open a new discussion with the correct requirement. Please Reply in that

Thanks,

Muthu

[Anil_Babu_Samineni]

Would you able to share sample