Having boolean true for certain hierarchy levels only

tasianna05
New Contributor

Having boolean true for certain hierarchy levels only

I have a hierarchy consisting three levels (product/product family/total products. Similar to the one in the trainings) and I tried to do some conditional formatting on it (need to use it as other tables on dashboard have list/line item subsets in it so needed to create a consistent outlook)

 

I thought using an if formula like this: IF Product only? THEN 0 ELSE IF Product family only? THEN 1 ELSE 2 would work but cannot put together a correct module which contains booleans for both hierarchy levels.

I need something like this one, but I only can figure out for two hierarchy levels. (this formula runs to error: Level mismatch on common dimension)

 

tasianna05_0-1627980239911.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
AjayM
Occasional Contributor

Re: Having boolean true for certain hierarchy levels only

Hi @tasianna05 ,
Hope this answers your concern.

1. Define count lineitems for P1 and P2 with different summaries (Sum and Formula respectively)

2. Add another lineitem 'P1 vs P2' with a summary ratio to gather different output for each level

Module BP1.png

3. You can use 'P1 vs P2' lineitem for CF or define another one like below
Module DV.png

View solution in original post

3 REPLIES 3
KirillKuznetsov
Certified Master Anaplanner

Re: Having boolean true for certain hierarchy levels only

Check Formula and Ratio summary methods: 

https://help.anaplan.com/9ab4e05a-1748-4027-babe-525d5bdd5b22-Summary-Method

Both can be helpful here.

AjayM
Occasional Contributor

Re: Having boolean true for certain hierarchy levels only

Hi @tasianna05 ,
Hope this answers your concern.

1. Define count lineitems for P1 and P2 with different summaries (Sum and Formula respectively)

2. Add another lineitem 'P1 vs P2' with a summary ratio to gather different output for each level

Module BP1.png

3. You can use 'P1 vs P2' lineitem for CF or define another one like below
Module DV.png

View solution in original post

tasianna05
New Contributor

Re: Having boolean true for certain hierarchy levels only

Hi @AjayM 

 

Yes it worked! Slight modification I've done as I do not have equal amount of products under the product family:

IF 'P1 vs P2' = 1 THEN 2 ELSE IF 'P1 vs P2' <> 'P1 vs P2'[SELECT: Top Level parent] THEN 1 ELSE 0

 

Thank you @AjayM and @KirillKuznetsov  the help!

 

Anna