5 Replies Latest reply on Nov 8, 2018 2:37 PM by Deepak Rai

# FIXED LOD (COUNTD) behavior with Hierarchy

Hi All,

I have a FIXED LOD calculation but I expect it to change based on the hierarchy expansion.

I have a hierarchy with two levels Name and Place.

The Ids for Direct Ad|Trip1 and Direct Ad|Trip2 are same except one (68417298) highlighted below.

Now, I want to get a DISTINCT count of ([Id]).

1. At Name level, I wish to get it as 6.

2. At Place level, I wish to get it as 6 for trip1 and 5 for trip2.

I can get both these to work but separately

#1 LOD Fixed by Name (Denominator)

#2 LOD Fixed by Name & Place (Denominator)

How can I accomplish both in a single calculation?

• ###### 1. Re: FIXED LOD (COUNTD) behavior with Hierarchy

I might be wrong, but LOD's work basically using FIXED Dimensions. I don't think you can do that in Hierarchy. Again, I might be wrong.

• ###### 2. Re: FIXED LOD (COUNTD) behavior with Hierarchy

Sagar,

What you can do to check if  the hierarchy is exploded, is to count the nb or rows of the table  and compare it to the size of Name . If equal you use one lod otherwise you use the other.

You get the number of rows with

WINDOW_MAX( index() )

computed table down

the size of Name with

size()

compute along Name

and the final calc would be

if [nbRows] = [NameSize]

then SUM([LOD Fixed by Name (Denominator)])

else SUM([LOD Fixed by Name & Place (Denominator)])

end

it is a nested calculation , make sure to set the computing on both calc

Michel

2 of 2 people found this helpful
• ###### 3. Re: FIXED LOD (COUNTD) behavior with Hierarchy

Hi Michael,

That was Great!!! Learnt a new thing Today from you.

Thanks

Deepak

• ###### 4. Re: FIXED LOD (COUNTD) behavior with Hierarchy

Thanks Deepak !

This one works because the different calculations used are lods.

Unfortunately, if we use the same table calculation but we want to apply a different computing depending on the level of the hierarchy, we are still stuck.

• ###### 5. Re: FIXED LOD (COUNTD) behavior with Hierarchy

Yes, What you finally Created was my initial thought also, but Somehow, I was missing on how to Switch over LODs. Anyways, It was Good Stuff from you as Always!!!