Count Distinct

[et21]

Hi All I got a question on Count Distinct, below is what I type in the query

if ([Presentation].[Projects].[Scope Health]= 'Not Stated')
then (1)
else if ([Presentation].[Projects].[Benefits Health] = 'Not Stated')
then (1)
else if ([Presentation].[Projects].[Schedule Health]= 'Not Stated')
then (1)
else if ([Presentation].[Projects].[Resource Health]= 'Not Stated')
then (1)
else (0)

I want it to count if each of the queries contains Not Stated but somehow its just show 1 instead of counting how many are not stated

[bus_pass_man]

Try re-writing that expression using the case function.

[et21]

I did tried to re-write it with case but soemhow its still not working

[cognostechie]

If the Data Item is marked as a character then it will show  1 because characters are not summed up. Check the properties of the Data Item , what's the Regular Aggregate property set to?

The statement can also be better:

Case
  When (
             [Presentation].[Projects].[Scope Health]= 'Not Stated'
                                    OR
             [Presentation].[Projects].[Benefits Health]= 'Not Stated'
                                    OR
             [Presentation].[Projects].[Schedule Health]= 'Not Stated'
                                    OR
             [Presentation].[Projects].[Resource Health]= 'Not Stated'
            )
    Then (1)
    Else (0)
End

[et21]

I did tried to set my Aggregate to total first to see if its sum up or not but somehow its still the same and then changed to count and still not working.

[cognostechie]

Check the actual data , how many rows does it show?

[et21]

the actual data is show as normal but its just not count how many that are not stated, its just show 1 or 0 thats it

[cognostechie]

I don't know how to help you, makes no sense.

[cognostechie]

Are you seeing 1 and 0 in the rows or in the total?

[et21]

in total

[cognostechie]

So you said earlier that it shows 1 or 0? What does that mean? It should show only one count so how come it shows both?

[et21]

my mistake its either show 1 or 0 but not the total

[cognostechie]

Check the data and see if the total should be more than 1.

If this is a Data Item in the report then the Regular Aggregate property should be set to Sum and if this is a Query Item in Framework Manager then the usage property should be set to Fact and Regular Aggregate property set to Sum/Total.

Beyond this, I don't know what is happening in your report.

[bus_pass_man]

What do you mean by

its either show 1 or 0 but not the total

?   

I'm assuming that you have the calculation in a list report, possibly with stuff from Projects.  For each row where the expression is true you see a 1 and for each you see a 0.  Is that a correct understanding?  What is the value of the summary? Have added a summary?

Check the usage property.   




I'm assuming this is a calculation you are creating in reporting. It might be a good idea to create this sort of calculation as a query item in the projects query subject in your FM model.  Among other things, you would not need to recreate the calculation in other reports and by being defined in one place, the expression would be consistent and inadvertent errors could be lessened.