topic Re: Help with writing expression in QlikView

Help with writing expression

aarohipatel — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I need help with writing the following conditions in QV expression of straight table.

Ratio1= If ([ABC]=0) Then 0 Else ([feild2]/[ABC])

ABC= count(field1)

Ratio2= If([ABC]=0) Then 0 Else ([XYZ]/[ABC])

ABC= count(field1)

XYZ= sum(field2)

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

Re: Help with writing expression

sunny_talwar — Mon, 14 Mar 2016 17:13:22 GMT

Would you be able to share some data with expected output? It would be easier to resolve this with the data, rather then trying to decode your provided code

Re: Help with writing expression

aarohipatel — Mon, 14 Mar 2016 18:20:42 GMT

Hi Sunny,

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)

66/3= 22

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

Thanks

Re: Help with writing expression

sunny_talwar — Mon, 14 Mar 2016 18:45:41 GMT

Can you try this for RATIO -> Alt((Field2/Count(ABC)), 0)

and RATIO2 -> Alt((Sum(XYZ)/Count(ABC)), 0)

Re: Help with writing expression

aarohipatel — Mon, 14 Mar 2016 19:08:40 GMT

Hi Sunny,

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.

Thanks

Re: Help with writing expression

sunny_talwar — Mon, 14 Mar 2016 19:27:40 GMT

Ya that would be very helpful

Re: Help with writing expression

aarohipatel — Wed, 16 Mar 2016 14:42:05 GMT

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%

Thanks

Re: Help with writing expression

sunny_talwar — Wed, 16 Mar 2016 20:29:00 GMT

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?

Re: Help with writing expression

aarohipatel — Wed, 16 Mar 2016 20:43:31 GMT

Hi Sunny, Condition 1 works. Yes, same data for condition2.

Thanks

Re: Help with writing expression

aarohipatel — Fri, 18 Mar 2016 21:32:52 GMT

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.

Condition4:

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.

Thanks!

Sample data for condition 4.xlsx8.9 K Preview

Re: Help with writing expression

sunny_talwar — Mon, 21 Mar 2016 09:53:44 GMT

Lets do this one at a time. Can you elaborate on your second condition again? Is ABC Avg a text that you want or is it the Avg of rating A, B and C? If it is the average, what are the values for ABC, how are they determined?

Re: Help with writing expression

aarohipatel — Mon, 21 Mar 2016 13:16:57 GMT

Hi Sunny,

Condition 2:

Rating is a field whose values are alphabets. In the attached Rating Example.xls, ' RATING' is the rating of the products.

Examples:

Rating of Tables is 'A', in expression results, I want to see: 'A Avg'

Rating of Chairs is 'B', in expression results, I want to see: ' B Avg'

Rating of Fans is 'C' , in expression results, I want to see: 'C Avg'

Rating of Bulbs is 'D', in expression results, I want to see: ' D-ABC Avg'

Rating of Sofa is 'E' , in expression results, I want to see: 'E Avg'

Rating of Beds is 'F', in expression results, I want to see: 'F-ABC Avg'

Rating of Sofa is 'G' , in expression results, I want to see: ' G Avg'

Rating of Sheets is null, in Expression results, I want to see: 'Not Rated'

All the expected results are text. 'A Avg' ; D-ABC Avg', 'Not Rated' are all texts.

Thanks

Re: Help with writing expression

sunny_talwar — Mon, 21 Mar 2016 13:26:29 GMT

Here is the expression for 2nd condition:

=If(IsNull(RATING), 'Not Rated', If(not Match(RATING, 'D', 'F'), RATING & ' Avg', RATING & '-ABC Avg'))

If this is what you want, lets move to the third requirement

Re: Help with writing expression

sunny_talwar — Mon, 21 Mar 2016 13:45:43 GMT

Your expression for third requirement:

Count(Event)/Count (TOTAL Event)

Re: Help with writing expression

sunny_talwar — Mon, 21 Mar 2016 14:13:57 GMT

4th and final requirement

Pay close attention to the data model here. As I added an As-of Table (The As-Of Table)

Re: Help with writing expression

aarohipatel — Tue, 22 Mar 2016 13:25:54 GMT

All the answers provided (by Sunny T) for all 4 conditions are correct.Could mark only one answer as correct so mentioning it in this post.

Thanks Sunny for your patience and for helping me with all 4 conditions.

Thanks

Aarohi

Re: Help with writing expression

sunny_talwar — Tue, 22 Mar 2016 14:10:04 GMT

No problem at all. I am glad I was able to help.

Just so you know, there are helpful answers -> Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny