topic Re: How to get positive values and negative values separated? in QlikView

How to get positive values and negative values separated?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to get positive values and negative values separated?

Digvijay_Singh — Tue, 30 May 2017 03:12:34 GMT

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

Re: How to get positive values and negative values separated?

Tue, 30 May 2017 03:34:23 GMT

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

Re: How to get positive values and negative values separated?

Sergey_Shuklin — Tue, 30 May 2017 03:40:29 GMT

Hello, Mathu!

Why you don't wonna use IF condition?

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

Re: How to get positive values and negative values separated?

satishkurra — Tue, 30 May 2017 03:46:10 GMT

‌for positive values use range Max function as

rangemax(yourexpression,0)

for negative values use range min function as

rangemin(yourexpression,0)

Re: How to get positive values and negative values separated?

Tue, 30 May 2017 04:03:33 GMT

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

Re: How to get positive values and negative values separated?

Sergey_Shuklin — Tue, 30 May 2017 04:08:47 GMT

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?

Re: How to get positive values and negative values separated?

SunilChauhan — Tue, 30 May 2017 05:14:48 GMT

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

Re: How to get positive values and negative values separated?

Tue, 30 May 2017 05:23:23 GMT

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

Re: How to get positive values and negative values separated?

Anil_Babu_Samineni — Tue, 30 May 2017 05:54:31 GMT

Would you able to share sample

Re: How to get positive values and negative values separated?

Wed, 31 May 2017 07:02:59 GMT

Hi Anil ,

i have Opened New Discussion in that i have explained it clearly and i have also attached sample qvw and excel data

kindly visit and Help me out of it

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

Thanks,

Muthu