Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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])
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]))
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]))
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
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.
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
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))
Hi Muthu,
You can restrict the Dimension values to be displayed based on the Expression using Dimension limits option
Regards,
Bhargav