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
Quote from: c8aj on 21 Nov 2017 07:01:32 AM
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:
count ( distinct
case when year = ?year? and owner = ?owner?
then [car_reg]
else null
end
)
Null values are ignored by aggregate functions.