Set Analysis and IF Statement value differs

[vikasshana]

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,

V

[rubenmarin]

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.

[vikasshana]

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

[rubenmarin]

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.

[Sierd_Boersma · Accepted solution]

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

[Lech_Miszkiewicz]

Hi. The issue is fundamental…

you are doing: if….count

and you should be doing:

count(if……)

[vikasshana]

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,

[marcus_sommer]

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.