Set Analysis Issue

[danimarc12]

Hello everyone!

I have an issue with Qlik Sense. I want to convert a SQL query in a expression in Qlik. This is my query:

SELECT
               COUNT(DISTINCT ID)
FROM livservizio
WHERE totqtaord > totgia AND evasa=1
GROUP BY sede

I text in qlik : sum(aggr(count(DISTINCT {< evasa={1}, totqtaord={">$(=totgia)"}>} codice),sede))

but it's not working. The problem is the condition "Where totqtaord > totgia".

How should I write it correctly?

[canerkan]

Hi Danimarc12,

maybe try something like this:

sum(aggr(count(DISTINCT {< evasa={1}, totqtaord={">totgia"}>} codice),sede))

Let me know if it helped.

Regards,

Can

[danimarc12]

Hi @canerkan ,

thanks for your answer but it doesn't work ☹️

[canerkan]

Hi Danimarc12,

can you provide some sample data and what value your variable $(totgia) has, so i can look into this?

Regards,

Can

[danimarc12]

this is my table, I want to count codice where totqtaord > totgia and evasa=1, grouped by sede

[canerkan]

Hi Danimarc1,

i can suggest the following solution to your problem:

Add this line to your loadscript:

Table:
Load
*,
If(totqtaord > totgia and evasa = 1, 1, 0) as flag
From ...

and then use this in your expression:

=sum(aggr(count(DISTINCT{<flag = {1}>} codice),sede))

Let me know if it helped.

Regards,

Can