how to separate the values of one column in to two tables?

[#1]

how to separate the values of one column into two tables?

the below table contains Average and it contains an average of both positive(+ve) as well as negative(-ve) values.

I need a separate table  for Positive(+ve) values and a separate table for negative(-ve) values.

Please Refer the below Screenshots for your reference:

The Expression used in the above table for Average AMPM values:

=num(If(Rolling_7_days.AMPMTemp='AM',

  Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL<[Rolling_7_days.TGB Name], [Rolling_7_days.File Date]> Rolling_7_days.Counts)

   /Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<  [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts)-1,

  Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<[Rolling_7_days.TGB Name],[Rolling_7_days.File Date1]> Rolling_7_days.Counts)

  / Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL< [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts) -1))

Dimensions:

I need an output Like this:

Anyone help Me so that I will be very helpful for my learning Process.

Thanks,

Muthu

[avinashelite]

try like this , create two separate straight  table and use the expression as below

if(sign(expression)=-1, expression)

this is for negative values

if(sign(expression)=1, expression)

this is for Positive values

[#3]

Hi Avinash,

thanks for your Response Avinash. the above Expression is not working for my Expression. can you sink it with my expression?

Can you try it with this Expression expression:

=Sum ({<AMPM = {'AM'}>}[Latest File]*[TGB Counts]) 

[tresesco]

May be like :

For +ve :

= If (Sum ({<AMPM = {'AM'}>}[Latest File]*[TGB Counts])>0, Sum ({<AMPM = {'AM'}>}[Latest File]*[TGB Counts]))

For -ve :

= If (Sum ({<AMPM = {'AM'}>}[Latest File]*[TGB Counts])<0, Sum ({<AMPM = {'AM'}>}[Latest File]*[TGB Counts]))

[jonathandienst]

A little simpler (and the metric is ony calculated once):

Positive:

=RangeMax(0, Sum({<AMPM = {'AM'}>} [Latest File] * [TGB Counts]))

Negative

=RangeMin(0, Sum({<AMPM = {'AM'}>} [Latest File] * [TGB Counts]))

[#6]

Thanks Tresesco.

it works fine.

but can you do it for this expression:

=num(If(Rolling_7_days.AMPMTemp='AM',

  Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL<[Rolling_7_days.TGB Name], [Rolling_7_days.File Date]> Rolling_7_days.Counts) /Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<  [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts)-1,

  Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<[Rolling_7_days.TGB Name],[Rolling_7_days.File Date1]> Rolling_7_days.Counts)/ Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL< [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts) -1))

The above Expression is giving output like this:(it gives both positive as well as negative values)

Need to separate  positive in one table and negative in another table

please Help with this expression,

Thanks,

Muthu

[tresesco]

Since the expressions are exactly same (copy-pasted), they would probably not get calculated twice. Am I missing something ? Anyway, it's certainly simpler and more acceptable.

[bhargav_bhat]

Hi Muthu Kumar,

You can restrict the dimension values to be displayed by using the dimension limit option available in Qlikview straight table

The below screenshot  contains the details

For positive  values

Set   Greater than  0

For negative values

Set   Less Than   0

HTH

Bhargav

[#9]

The blow marked column is not based on dimension it is based on Expression

the Expression is :

=num(If(Rolling_7_days.AMPMTemp='AM',

  Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL<[Rolling_7_days.TGB Name], [Rolling_7_days.File Date]> Rolling_7_days.Counts) /Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<  [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts)-1,

  Only({<Rolling_7_days.AMPMTemp = {AM}>} TOTAL<[Rolling_7_days.TGB Name],[Rolling_7_days.File Date1]> Rolling_7_days.Counts)/ Only({<Rolling_7_days.AMPMTemp = {PM}>} TOTAL< [Rolling_7_days.AMI TGB ID]> Rolling_7_days.Counts) -1))

[bhargav_bhat]

Hi Muthu,

You can restrict the Dimension values to be displayed based on the Expression  using Dimension limits option

Regards,

Bhargav