Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
susanflarsen
New Contributor

Create a Field from an Expression and creating Set Analysis

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))
 
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Create a Field from an Expression and creating Set Analysis

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

View solution in original post

14 Replies
sergio0592
Valued Contributor II

Re: Create a Field from an Expression and creating Set Analysis

Hi,

You can create a field with using preceding load.

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534 

LOAD 
Field1,
Field2,
sum(GainedPoints)/sum(TotalPoints) as TotalQScore

INLINE [
Field1, Field2,GainedPoints,TotalPoints
UK, London,34,123
Russia, Moscow 22,89
USA,Dallas, 45, 130
]
Group by Field1,
Field2
;
susanflarsen
New Contributor

Re: Create a Field from an Expression and creating Set Analysis

I don't quite understand your response.  I do have GainedPoints and TotalPoints as existing fields in my load script currently (with data values of 1-100).  So I don't think  the INLINE table would be needed. 

When I paste the text your suggested [(sum(GainedPoints)/sum(TotalPoints)) as TotalQScore] into the bottom of that same Load script, I get an error when the load runs that says "Invalid expression", but if I click on OK, the load will continue to run to end and the new field TotalQScore does exist (but has no content).

Thoughts?

sergio0592
Valued Contributor II

Re: Create a Field from an Expression and creating Set Analysis

The load inline instruction was just for a quik sample in my post. I assume that you are using SQL Statement.

Can you post your sript?

susanflarsen
New Contributor

Re: Create a Field from an Expression and creating Set Analysis

I don't know how to write SQL statements, but this is my load script:

LOAD ID,
UniqueActivityID,
PolicyNo,
ActivityCompleter as Reviewee,
ActivityCompleterID,
CompletionDate,
WorkType,
QARemarks,
QustionID as QuestionID,
Question,
QAAnswer,
TotalPoints,
GainedPoints,
QACount,
DPUError,
DPOError,
QAType,
QADate,
QADateTime,
QAName,
QAID,
AHT,
CaseStatus,
(sum(GainedPoints)/sum(TotalPoints)) as TotalQAScore,
'Ask Unum SS' as Role
FROM
[\\Fsdfs3dat01p\dept\Continuous Improvement\EXL Exports for Qlikview\QMS AU Completed Audits Data-prior to last month.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Export_To_Excel_Audits);

Taoufiq_ZARRA
Valued Contributor

Re: Create a Field from an Expression and creating Set Analysis

Susan, why do you want to add it to the script?


the formula appears simple, I don't find the need to put it in the script.

 

So for the 1: try to use a variable where you store the value of (sum(GainedPoints))/(sum(TotalPoints)) with the label TotalQAScore

Otherwise for the second question, you can use the same variable in Set Analysis.

Share a sample data and give an example.

Cheers,

Taoufiq


Taoufiq ZARRA
sergio0592
Valued Contributor II

Re: Create a Field from an Expression and creating Set Analysis

You use sum(), so you have to use group by statement at the end.

yassinemhadhbi
New Contributor III

Re: Create a Field from an Expression and creating Set Analysis

Good morning,

I propose you create a variable and you put the expression in it 

sergio0592
Valued Contributor II

Re: Create a Field from an Expression and creating Set Analysis

So try with, does it works?

LOAD
ID,
UniqueActivityID,
PolicyNo,
ActivityCompleter as Reviewee,
ActivityCompleterID,
CompletionDate,
WorkType,
QARemarks,
QustionID as QuestionID,
Question,
QAAnswer,
TotalPoints,
GainedPoints,
QACount,
DPUError,
DPOError,
QAType,
QADate,
QADateTime,
QAName,
QAID,
AHT,
CaseStatus,
(sum(GainedPoints)/sum(TotalPoints)) as TotalQAScore,
'Ask Unum SS' as Role
FROM
[\\Fsdfs3dat01p\dept\Continuous Improvement\EXL Exports for Qlikview\QMS AU Completed Audits Data-prior to last month.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Export_To_Excel_Audits)
group by
ID,
UniqueActivityID,
PolicyNo,
ActivityCompleter as Reviewee,
ActivityCompleterID,
CompletionDate,
WorkType,
QARemarks,
QustionID as QuestionID,
Question,
QAAnswer,
TotalPoints,
GainedPoints,
QACount,
DPUError,
DPOError,
QAType,
QADate,
QADateTime,
QAName,
QAID,
AHT,
CaseStatus,
'Ask Unum SS' as Role
;

 

susanflarsen
New Contributor

Re: Create a Field from an Expression and creating Set Analysis

No, Sergio.  Unfortunately, I still receive Invalid Expression error when I run the load script as copied from what you suggested.