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

[sergio0592]

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]

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]

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]

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]

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

[sergio0592]

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

[yassinemhadhbi]

Good morning,

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

[sergio0592]

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]

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