topic Re: expression in set analysis in QlikView

expression in set analysis

curiousfellow — Mon, 26 Feb 2024 09:57:04 GMT

I have a chart with following set up :

Dimension1

Dimension 2

expression1: if(dimension1 = 10, count(distinct employee),

if(dimension1 = 20, count(distinct {<city = {'Bostom'}>} employee,

expression2: if(dimension1 = 10, count(distinct {<

dimension1= {"= sum(sales) > 9000"}>}

if (dimension1= 20 ,

count(distinct {<

dimension1= {"= sum(result) > 800"}>}

>}employee)

))

Expression 1 works fine, but expression two shows only a value when I select dimension1 in a listbox.

It probably has something to do with dimension1 in the set modifier of expression2 but I cannot find how to solve this. I tried various options but the result is the same or shows no values at all.

Disclaimer: it is possible I forgot some parentheses in this simplified example but I hope you understand what the issue is

Re: expression in set analysis

F_B — Mon, 26 Feb 2024 11:02:11 GMT

Hi @curiousfellow,

for what i see in your description, if dimension1 equals to 10, it can't also equal to "sum(sales) > 9000",

as well as if dimension1 equals to 20, it can't also equal to "sum(result) > 800".

Re: expression in set analysis

curiousfellow — Mon, 26 Feb 2024 13:45:01 GMT

Seems logic 🙂 I must admit that I don't really understand how to use these kind of expressions in set analysis. What should I use instead of dimension1 in dimension1= {"= sum(sales) > 9000"}>}

I tried several values/dimensions but none of them works.

When I search this forum it seems that for dimension1 a random dimension or value is used.

Re: expression in set analysis

F_B — Mon, 26 Feb 2024 13:55:21 GMT

Set analysis is a very powerful tool and I earnestly advise you to look at it:

Tutorial - Creating a set expression | Qlik Sense on Windows Help

However, taking one step back, can you explain better what is the purpose of "expression2" and what fields are involved? What kind of chart are you inserting that calculations and what would you like to show?

Re: expression in set analysis

curiousfellow — Mon, 26 Feb 2024 14:42:17 GMT

In my chart I show a various number of KPI. Every KPI has it own expression / formula.

The expression to use is determined by the condition If(linenumber = xxxx, expression

One of the expression counts the number of employees who have a higher profit_percentage than the standard percentage. The profit_percentage should be calculated as an identifier.

In the tutorial you are referring to no expression is used.

I am trying the same as is shown here https://community.qlik.com/t5/QlikView-App-Dev/Using-an-expression-within-Set-Analysis/td-p/910258 but I do not know what I should use instead of [session id]

Re: expression in set analysis

curiousfellow — Tue, 27 Feb 2024 12:38:40 GMT

Think I've solved it. 
Instead of dimension1 in dimension1= {"= sum(sales) > 9000"}>} I used a random field from my table that is not used in an expression or as a dimension in my chart.
I still don't understand the logic of this, so if anyone could explain this I would be very grateful.

Re: expression in set analysis

F_B — Tue, 27 Feb 2024 12:40:21 GMT

In your case, [session id] should be a field in your data model that you didn't mention, or that you will need to create.