Help with Counts/Totals

[Skykit]

Hi all,
I'm going round the bend with what should be simple enough.  Numbers below indicate data items:
1. Type - this is a case statement - when desc in ('car') then Motor else 'not car'.
2. Total - This is a case statement - when year = ?year? and owner = ?owner? then count(distinct[car_reg]

These both work fine and are correct.  The issue is now when any further calculations go in.  so the next data item is exactly the same as total however there is an additional bit of criteria : when model in 'Audi', 'BMW', 'Mercedes'.  When I add this, the results I get back is the same as the Total.

I have tried using count(distinct[car_reg]) FOR [Type]
I have tried changing the agg method (currently automatic/automatic)
I have tried solve orders setting the Type to 1
I have tried splitting it out so the Model part is done in another item and the final data item simply calls it - ie:AND [model] = 'German'

This is currently in a list report but crosstab shows the same issue.
All it should really show is :

Count of German vehicles , Count by colour white and Count by colour Black vehicles by Type followed by a final data item that uses all 3 of these - something along the lines of another case statement to say when year = 2017, owner = joe bloggs, black >5 and german vehicles > 10 then give me a total number of car registrations.

What I don't understand is why all the new calculations are bringing back the same value.

Thanks in advance

[Lynn]

Hi all,
I'm going round the bend with what should be simple enough.  Numbers below indicate data items:
1. Type - this is a case statement - when desc in ('car') then Motor else 'not car'.
2. Total - This is a case statement - when year = ?year? and owner = ?owner? then count(distinct[car_reg]

These both work fine and are correct.  The issue is now when any further calculations go in.  so the next data item is exactly the same as total however there is an additional bit of criteria : when model in 'Audi', 'BMW', 'Mercedes'.  When I add this, the results I get back is the same as the Total.

I have tried using count(distinct[car_reg]) FOR [Type]
I have tried changing the agg method (currently automatic/automatic)
I have tried solve orders setting the Type to 1
I have tried splitting it out so the Model part is done in another item and the final data item simply calls it - ie:AND [model] = 'German'

This is currently in a list report but crosstab shows the same issue.
All it should really show is :

Count of German vehicles , Count by colour white and Count by colour Black vehicles by Type followed by a final data item that uses all 3 of these - something along the lines of another case statement to say when year = 2017, owner = joe bloggs, black >5 and german vehicles > 10 then give me a total number of car registrations.

What I don't understand is why all the new calculations are bringing back the same value.

Thanks in advance

This is a relational source I presume.

Try setting up your expressions like this:

Code Select Expand


count ( distinct
  case when year = ?year? and owner = ?owner?
      then [car_reg]
      else null
  end
)


Null values are ignored by aggregate functions.