Having Trouble creating a new Field (Help would be greatly appreciated)

[#1]

Hello all,

Rather new to the qlikview community and qlikview in general and I'm having trouble creating a new field. Here is what I'm working with:

I am trying to do a customer segmentation and segment them based on how many transactions the client does and which side of the transaction the client is on.

For example, I have two fields I'm working with, the payee and payor, I generated the count of payee and payor through this script:

Payor:

load Payor as Company,

     count(Payor) as 'Payor Count'

from

(ooxml, embedded labels)

where Type = 'Payment'

group by Payor;

Payee:

LOAD  Payee as Company,

      count(Payee) as 'Payee Count'

from

(ooxml, embedded labels)

where Type = 'Payment'

group by Payee;

Through this script I was able to attain a count of Payee and Payor respectively.

Then I created a new list box called Total Count through the expression:

=if(isnull([Payee Count]), 0, [Payee Count]) + if(isnull([Payor Count]), 0, [Payor Count])

This expression gave me the sum of Payee and Payor Count per client (well its linked to client anyways)

Finally I created another List box called Payor Percentage that is generated through this expression:

=if(isnull([Payor Count]), 0, [Payor Count])/ (if(isnull([Payee Count]), 0, [Payee Count]) + if(isnull([Payor Count]), 0, [Payor Count]))

This gave me the percentage of how much each client is a payor. With these elements I am now able to segment my customers. My problem is that I am not able to create a new field that takes the payor percentage and segments them into a value.

So essentially what Im saying I wanna create a field that will function off of this statement or something like it:

if([Payor Percentage]>= '.7', 'Major Buyer', if([Payor Percentage]<= '.3', 'Major Vendor', 'Buyer and Vendor'))

My key problems are:

I can't seem to identify the fields I created through Expressions (or rather don't know how to identify them)

Because I can't identify these fields I have had no luck being able to create the essential variable I want.

Any help would be greatly appreciated. Thank you so much for your time.

Cheers

P.S.

I have Qlikview Personal Edition so show or recieve qvw documents

[Gysbert_Wassenaar · Accepted solution]

You can't reference expressions in other objects. You'll have to recreate the expression in the other object. You could use variables to hold the expression. You can then use the variable everywhere in place of the expression.

For example you have an expression rangesum([Payee Count],[Payor Count]) you want to use in a number of charts. By creating a variable vPayCount and giving it the expression as value you can then use it in expressions: alt([Payor Count],0)/$(vPayCount). The $(vPayCount) will be expanded so the expression will be evaluated as alt([Payor Count],0)/rangesum([Payee Count],[Payor Count])

In a chart object (e.g. a straight table) you can give the expression labels a value. You can then use the labels in other expressions in the chart object. You can also use column references. The first expression is Column(1), the second Column(2) etc.

You can replace this if statement:

    =if(isnull([Payee Count]), 0, [Payee Count]) + if(isnull([Payor Count]), 0, [Payor Count])

with this:

    rangesum([Payee Count],[Payor Count])

which is both shorter and doesn't incur the performance penalty of the if's.

[padmanabhan_ram]

Try to store the Payee count, Payee percentage in Variables. then use that variable in the set analysis like if(vPayorPercentage >='.7','Major Vendor',0).... where vPayorPercentage is a variable.

[#4]

Thank you both very much, I was able to get those references to work!! Thanks!!!

[#5]

I actually came across another problem doe. Apparently my Customer Segment list box is not completely associating all my clientele.

For example, I was able to use

=if(alt([Payor Count],0)/rangesum([Payee Count],[Payor Count])>= .7,'Major Buyer',

if (alt([Payor Count],0)/rangesum([Payee Count],[Payor Count]) <= .3, 'Major Vendor','Both Buyer and Vendor'))

for the expression on the list box. I have two problems with this:

1) It doesn't highlight any of my major buyers that essentially are 100% Buyers/Vendors (or rather if they have 0 [or null] on the payor or payee side) even though if I click on those companies that are 100% Buyers/Vendors it'll recognize them as a Major buyer, but if I tried to count Major buyers lets say it'll only recognize the one that has both a payee and payor count.

2) I also wasn't able to get this to work in my variable expression, so it seems like I can't make it a variable

This is a major problem because my goal is to get these categories major buyer/major vendor/ both buyer and vendor into a buy chart but I can't accurately count the amount of Major Buyers/Vendors/Both Buyer and Vendor (help with this expression would be great to, again im rather new).

Again Thanks for your help in advance

[#6]

bump