0

I've been struggling creating the following view but I'm so close.

  • I have a dataset with three columns of data where each level is broader than the other
  • I want to be able to filter the data dynamically by two of the columns
  • I want to create a visual where you can see the grouping clearly. I managed to create the two level hierarchy but I'm struggling to get the third.
  • As you will see I'm using QUERY in order to use only one formula to power everything

enter image description here

Here's the formula I'm using for the two level view, but how can I modify it so the output includes the three levels? Here's the spreadsheet that you can use as example, with my own function. Appreciate any input!

=ArrayFormula({
IF(

QUERY(SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col2")="",

QUERY(SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col1"),""),

QUERY(
SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col2")})
  • Try Pivot table (columns L:N in your file) in case using formula isn't strict requirement. – Oleg_S Oct 1 at 8:02
  • Almost a duplicate - How would you create a dynamic visualization with two hierarchical categories? on Stack Overflow. Read this post and then comment here if you're still not sure how to proceed, BTW, is this homework? – Tedinoz Oct 1 at 8:22
  • OlegS using a formula is a strict requirement but thank you for the suggestion! @tedinoz I read that post and kind of used their approach, but I need a three level vs the two level they have. It's not homework but I'm helping someone. Would really appreciate any help, thanks! – Bob Oct 1 at 17:47
  • @Mel from StackOverflow seems to be using the exact same data as you - quite a co-incidence, don't you think. I wonder whether Mel has any thought on creating a third tier. – Tedinoz Oct 2 at 3:14
  • @tedinoz I have different data, but copied the sheet in the other post to illustrate what I need – Bob Oct 2 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.