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

Create a Field from an Expression and creating Set Analysis

susanflarsen — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sergio0592 — Tue, 26 Nov 2019 21:36:50 GMT

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 ;

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

susanflarsen — Wed, 27 Nov 2019 13:46:08 GMT

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?

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

sergio0592 — Wed, 27 Nov 2019 13:58:09 GMT

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?

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

susanflarsen — Wed, 27 Nov 2019 14:03:45 GMT

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

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

Taoufiq_Zarra — Wed, 27 Nov 2019 14:08:05 GMT

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

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

sergio0592 — Wed, 27 Nov 2019 14:12:33 GMT

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

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

yassinemhadhbi — Wed, 27 Nov 2019 14:28:39 GMT

Good morning,

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

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

sergio0592 — Wed, 27 Nov 2019 15:20:50 GMT

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 ;

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

susanflarsen — Wed, 27 Nov 2019 15:58:03 GMT

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

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

susanflarsen — Wed, 27 Nov 2019 16:19:47 GMT

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.

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

rwunderlich — Wed, 27 Nov 2019 17:28:43 GMT

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

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

susanflarsen — Mon, 02 Dec 2019 16:26:57 GMT

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.

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

rwunderlich — Wed, 04 Dec 2019 00:30:57 GMT

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

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

susanflarsen — Wed, 04 Dec 2019 16:58:50 GMT

Success! Thank you for your help, Rob!