Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
susanflarsen
Contributor
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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Specialist III
Specialist III

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
Contributor
Contributor
Author

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
Specialist III
Specialist III

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
Contributor
Contributor
Author

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sergio0592
Specialist III
Specialist III

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

yassinemhadhbi
Creator II
Creator II

Good morning,

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

Best Regards
Yassine Mhadhbi
sergio0592
Specialist III
Specialist III

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
Contributor
Contributor
Author

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