Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get positive values and negative values separated?

Hi All,

The below table box is the output I am getting

The average value is the output based on date and value

NameDateValueAvg.Value.
A1/1/2017PM40
B1/2/2017PM70.75
C1/3/2017PM4-0.42
D1/4/2017PM3

-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


1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

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?

View solution in original post

10 Replies
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

Not applicable
Author

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 :

NameDateValueAvg.Value.
A1/1/2017PM40
B1/2/2017PM70.75

Negative Straight table:

NameDateValueAvg.Value.
C1/3/2017PM4-0.42
D1/4/2017PM3

-0.25

Thanks,

Muthu

Sergey_Shuklin
Specialist
Specialist

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
Specialist II
Specialist II

‌for positive values use range Max function as

rangemax(yourexpression,0)

for negative values use range min function as

rangemin(yourexpression,0)

Not applicable
Author

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

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
Champion II
Champion II

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

Sunil Chauhan
Not applicable
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful