9 Replies Latest reply on Feb 14, 2020 10:04 AM by Jim Dehner

# LOD and IF boolean clause error!

Let me describe my problem:

I have observation data from multiple "snapshots" throughout the week of a building.

I'm trying to calculate the amount of DESKS per team on a my dataset.
One of the columns is called [predefined] which is a number that corresponds to a desk number which has been predefined on the set up. However, even if an entry is not on a desk it will still get a [predefined] number. Hence, I've created a boolean calculation that determines whether it's or not a desk.

I'm ten trying to add all my desks per team with the following calculation but have not been able:

{ FIXED [Team Name]:

if int([Desk])=1 then COUNTD([predefinedid]) ELSE 0 END}

This blogpost seemed to solve it but I couldn't replicate the solution.

• ###### 1. Re: LOD and IF boolean clause error!

{ FIXED [Team Name]:COUNTD(if int([Desk])=1 then ([predefinedid])  END)}

• ###### 2. Re: LOD and IF boolean clause error!

Hi Greg

The error was it an Aggregation v non-aggregation error?

really like to see you twbx workbook to see if there are other calculations involved

but it looks like you would need to aggregate the Desk with Min(Desk) or Max(Desk)

see See it your way: FAQ Series- Cannot Mix Aggregate and Non-aggregate   for examples of aggregations errors

Jim

• ###### 3. Re: LOD and IF boolean clause error!

Hi Jim Dehner,

I've uploaded my workbook for reference.

The WIP worksheet is the one that I'm trying to use to develop some of the measures before actually using them into a visualisation. Just so I make sure that I'm using the correct numbers.

Ultimatetly this measure about the amount of seat per team is simply going to go on a percentage of occupancy per team (there's already another tab for that). Where I can look at the percentage of use of those desks per team is.... Oh well that another LOD that i have to solve (the bottom is closer to the number I need but when I try my LOD I get wrong number as you can see.... Probably another post.

Anyway, many thanks

Speak soon

• ###### 4. Re: LOD and IF boolean clause error!

Hi

cool workbook

I changed this to get rid of the ATTR - cant use attr in an LOD

then wrote the LOD as

don't know what else you are doing the it but at least it will get you past the error

Jim

1 of 1 people found this helpful
• ###### 5. Re: LOD and IF boolean clause error!

Thanks Jim.

I'm not sure my logic really understands what the ATTR function does. But yes it gets if of the error in the error in the LOD calculation.
However, it does not calculate what I need.

for example in this Co Sec Team the added Desks per team should be 3 (orange = desk) not 5 as it's happening...

hummm getting close but not quite !!

• ###### 6. Re: LOD and IF boolean clause error!

attr is used where values can have multiple values - think like strings or dates - it is a way to aggregate a dimension that you don't necessarily think of as a aggregation

other ways to aggregate them would include Min(), Max()

now the 3 or 5 - is the count date or department or some other dimension dependent - if so you will need to add it into the dimension list in the LOD before the colon

Jim

1 of 1 people found this helpful
• ###### 7. Re: LOD and IF boolean clause error!

JIm,

Thanks for the bonus lesson on ATTR. I think your example makes it clearer.

Regarding the count that I'm trying to do in fact it's not really dependent of any other measure.
I mean, there's only a a Hierarchy that goes TEAM NAME >> PREDEFINED  and all I need is the COUNTD (distinct) per TEAM NAME of those values that are actually DESKS ( the boolean measure that you helped me fix).

• ###### 8. Re: LOD and IF boolean clause error!

JIM you made me realise that in fact there is...

This seems to do ALMOST what I need  :

{ FIXED [Team Name], [Type Name]:

if [Desk]=true

THEN COUNTD([predefinedid])

ELSE NULL

END}

Though, despite the "ELSE NULL" I'm still getting a count !