topic Re: Set Analysis Issue in App Development

Set Analysis Issue

danimarc12 — Wed, 26 Jan 2022 12:24:07 GMT

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 — Wed, 26 Jan 2022 12:25:09 GMT

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 — Wed, 26 Jan 2022 12:39:56 GMT

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

canerkan — Wed, 26 Jan 2022 12:56:08 GMT

Hi Danimarc12,

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

Regards,

Can

danimarc12 — Wed, 26 Jan 2022 13:09:18 GMT

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

canerkan — Wed, 26 Jan 2022 15:41:44 GMT

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