Create a Field from an Expression and creating Set Analysis

[susanflarsen]

How can I take a Qlikview expression and make it into a permanent Field of data that can be re-used in other expressions?  My expression is: (sum(GainedPoints))/(sum(TotalPoints)), which I express in my chart as a percentage (example: 4/5=80.0%).  I label this expression "Total QA Score" in my particular chart, but I want a way to use that label as a field name [TotalQAScore] so I can use it over and over in other charts/expressions and also as a modifier in set analysis.

1. How can I create this expression as a Field in my data/load script?

2.  Also, how would I create a set analysis statement if I want to take the following measure and only display results if the TotalQAScore=100.0%:  =count(DISTINCT(UniqueActivityID))

[susanflarsen]

Hi Taoufig,

I've added a variable in the Settings/Variable Overview screen now called vTotalQAScore, and indicated that the definition of this is (sum(GainedPoints))/(sum(TotalPoints)*100).

How can I use this variable to write a set analysis so that my system will count distinct UniqueActivityID (i.e. =count(DISTINCT(UniqueActivityID))) but only if the vTotalQAScore is equal to 100.0?  I'll also need to create a set analysis to count all of those scores that are not equal to 100.0.

I am new to set analysis.

[rwunderlich]

You will want to store the expression in a variable.   You can create the variable overview or in the script:

SET TotalQAScore] = (sum(GainedPoints))/(sum(TotalPoints));  

Then you can use it in chart expressions as:

$(TotalQAScore)

Re your set analysis question.  I'll assume you have only 1 row per UniqueActivityID and therefore do not need to sum() before calculating GainedPoints / TotalPoints.  Let me know if I'm wrong.

count(DISTINCT {<UniqueActivityID={"=GainedPoints / TotalPoints = 1"}>} UniqueActivityID)

(Note there is no paren after the keyword "DISTINCT")

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[susanflarsen]

Hi Rob,

Thanks for your suggestions. 

Yes, my data does contain more than  1 row per UniqueActivityID, so I'd appreciate your recommendation of what to modify in the second syntax you suggested. 

Also, with the first syntax that you provided (SET TotalQAScore] = (sum(GainedPoints))/(sum(TotalPoints));  ), is there any typo in that?  I did copy and paste that syntax into my load script (once with the "]" and once without the "]", but after I refresh my data, the field "TotalQAScore" doesn't show up in my chart dimensions "Available Fields/Groups" list.  I welcome any additional insight.

[rwunderlich · Accepted solution]

Hi Sue,

Yes, there was a typo in my previous post. The statement should be:

SET TotalQAScore = (sum(GainedPoints) / sum(TotalPoints)); 

TotalQAScore will not show up in the Field list. It is a variable, so it will show up in the Variable dropdown in the Expression Editor. 

Since you have multiple rows per UniqueActivityID, you will have to use an aggr in your set analysis modifier. The expression return the aggregated value by UniqueActivityID, using the variable, is:

aggr($(TotalQAScore), UniqueActivityID)

The expression to count the ids where result = 1 is:

count({<UniqueActivityID={"=aggr($(TotalQAScore), UniqueActivityID) = 1"}>}DISTINCT UniqueActivityID)

to count the "not 1":

count({<UniqueActivityID-={"=aggr($(TotalQAScore), UniqueActivityID) = 1"}>}DISTINCT UniqueActivityID)

The only difference between the two being the "=" or "-=".

Attached is a qvw that demonstrates. If this example does not work for your data, please update it to show your data problem and repost. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[susanflarsen]

Success!  Thank you for your help, Rob!