I need help with writing the following conditions in QV expression of straight table.
Ratio1= If ([ABC]=0) Then 0 Else ([feild2]/[ABC])
Ratio2= If([ABC]=0) Then 0 Else ([XYZ]/[ABC])
UVW =If(([Count(field)]-[field2])=0) Then 0 Else ([field3]/([Count(field1)]-[field2]))
LMN = If( ([field 1] In List ("A" ; "B")) )Then "10" Else If( ([field1] InList("D" ; "F")) )Then "15" ElseIf( ([field1]="C") )Then "12" Else "N/A"
Thanks in Advance
Solved! Go to Solution.
I tried to provide some data through screenshots. Hope it works
In this , Count (ABC) and field 2 has its values in #’s.
In the Ratio column, I would expect to see that: if Count(ABC) is ‘0’ then RATIO should display ‘0’ orelse it should display (33/3) which is 11. I want to see '11' in RATIO column
Ratio2 I would like to see ‘0’ if Count(ABC)=0 orelse it should display Sum(XYZ)/Count(ABC)
'Field 3 result' what I want to see is If field 3 is A or B then result should show ‘10’, if field 3 is ‘C’ result should show 12, if field 3 is D or F, result should show 15 if field 3 is not A,B,C,D,F result should show’N/A’
I am not able to see the expected results using the above expressions.
I will try to create some test data and share it with you so that you will be able to help me.
Hi Sunny, I have attached the files with some sample data.
Condition 1: expected result
If Rating is either A or B result should show ‘5’
If Rating is either D or F result should show ‘10’
If Rating is C result should show ‘15’
If rating is neither A nor B nor D nor F nor C then result should show ‘N/A’
Condition2 : expected result
If Rating is null, it should show ‘ Not Rated’
If Rating is either D or F it should show that particular rating –ABC Avg
If 😧 D-ABC Avg
If F: F-ABC Avg
Anything apart from above conditions it should show that particular rating Avg
Example: if rating is G it should show : G Avg
Condition 3 expected result( %): Event Example.qvw
What I am expecting in the result is :
We have count (ID) of Event A =2 and Total count of ID’s= 5
Expected result = 2/5*100= 40%
Similarly Event B expected result= 3/5*100= 60%
For condition 1, you can use this expression:
=If(IsNull(RATING), 'N/A', Pick(Match(RATING, 'A', 'B', 'C', 'D', 'F') + 1, 'N/A', 5, 5, 15, 10, 10))
For condition 2, where do I get the numbers from? Condition1?
Hi Sunny, any update on the 2nd and 3rd condition?
I tried this for condition 2:
=If(IsNull(Rating),'Not Rated', if(Rating= Valuelist( ‘D’,'F'),Concat(Rating,'-PAR Avg'),Concat(Rating,'Avg')))
But it is not working. Please suggest
Also, I need help with one more condition.
If I select 2016/01, the variance should show Count(2016/01 Request ID) - Count(2015/01 Request ID)
Example: 2016/01 Count( Request ID= 4 and Count(2015/01 Request ID ) = 5
Variance= 4-5= -1
What I am trying to achieve is I am calculating the variance of current year and prior year for the same month. Attached is some sample info.
Looking forward for your reply.