topic Re: Set Analysis and IF Statement value differs in QlikView

Set Analysis and IF Statement value differs

vikasshana — Fri, 06 Jun 2025 20:08:08 GMT

Hi,

I've below sample data.

ID ID1 Period Type Type1

1 12 2023004 1 1

1 13 2023004 1 0

2 14 2023005 1 1

In the Front End I'm using the below set analysis expression and I got the output as '2'

=count(Distinct{<Type={'1'},Period-={'""'}>}ID)

I'm trying to migrate the above set analysis to backend script and for that I'm using below IF condition.

If(Type = '1' AND Period <> '""',Count(DISTINCT ID)) as NewValue

and sum(NewValue) I got the output as '3'.

In Set analysis I got output as '2' where as by using IF condition I got output as '3'. Can you help me understand where it is going wrong.

Regards,

Re: Set Analysis and IF Statement value differs

rubenmarin — Sat, 07 Jun 2025 07:13:06 GMT

Hi, the set analysis expression is calculated for all t he data, so the 'distinct ID' clause is only counting the ID 1 one time.

On the other hand I suppose NewValue is an added field to the table, or in a table with more than one row for ID=1, so the value of NewValue is 1 for each rows with ID=1, so the sum is 2.

You can check if this what is happening selecting the ID 1. The result of "sum(NewValue)" is 2?

In this case you will need to group in some way that IDs are not repeated in different rows.

Re: Set Analysis and IF Statement value differs

vikasshana — Sat, 07 Jun 2025 08:54:19 GMT

Yes if I select ID 1 I see "sum(NewValue)" is 2. Any idea how to fix it?

Re: Set Analysis and IF Statement value differs

rubenmarin — Sat, 07 Jun 2025 10:20:57 GMT

I have very little information of the data behind and the possible selections, maybe you can try loading another table that groups by ID or Period, but if an ID can appear in different periods, and you want to count the distintic ID in the period selected by the user, I don't think you can precalculate that.

Re: Set Analysis and IF Statement value differs

Sierd_Boersma — Sun, 08 Jun 2025 06:51:02 GMT

If you want to do this is the script you will loss the dimention periode because will will aggergate the data. In the script you cant do that with a if statement but with a group by in the load statement. A if state wil only do a count on record level

Re: Set Analysis and IF Statement value differs

Lech_Miszkiewicz — Sun, 08 Jun 2025 07:04:59 GMT

Hi. The issue is fundamental…

you are doing: if….count

and you should be doing:

count(if……)

Re: Set Analysis and IF Statement value differs

vikasshana — Sun, 08 Jun 2025 07:41:02 GMT

I tried below two, but still the same

Count(DISTINCT If(TotalContractCount = '1' AND EndDate_Period_Scope <> '""',CONTRACT_ID)) as Denominator,

OR

Count(If(TotalContractCount = '1' AND EndDate_Period_Scope <> '""',CONTRACT_ID)) as Denominator,

Re: Set Analysis and IF Statement value differs

marcus_sommer — Tue, 10 Jun 2025 09:47:34 GMT

Make sure that you don't count a key-field - this might be done by keeping the ID within the fact-table and replacing the key to a dimension with an autonumber-field.