1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer
Knowledge Base

Hide Columns based on heading not position

Jason Ash ·

I have a user that wants the layout below with two dimensions on each axis and two measures.

The measure Sales Budget will only be populated for year 2021 green box and the other years orange box will always have nothing in them.

Is it possible to hide the Sales Budget based on looking at the dimension value 2021?

I believe I can't use position or end calculated lookup columns because if the user changes the criteria all the positions will change.

0

Comments

5 comments
Sort by DateVotes
  • Marlene Knaup

    Hi John, 

    I just have the following idea. Create a calculated column for Sales Budget in 2021, make the measure Sales Budget invisible and reorder the calculated measure to the right position.

     

    0
  • Jason Ash

    Hi Marlene,

    Good shout I'll give it a go but I think could be an issue when criteria changes and existing columns move. i.e. in image above location 003 closed so doesn't have any data/columns for year 2021 but they still want to see it as it had sales in years 2020 and 2019.

    0
  • Louise Stub Hansen

    Maybe you could use absolute references in your calculated columns so you are sure to get 2021 and end the syntax with " ,0" to avoid problems if 2021 are missing?

    https://community.targit.com/hc/en-us/community/posts/360014784338-Absolute-Reference-Calculation

     

    0
  • Louise Stub Hansen

    Or maybe you could make a visibility agent of each location and year => hiding the column if sum of sales budget are 0?

    Agent syntax: sum(@"[003]";"[2020]", all, m2, 0) = 0.

    Remember to change action from hide value to hide column member. 

    But of course it depends how many locations and year you have got :-)

    0
  • Jason Ash

    Thanks Louise,

    Yeah issue is it is 100 Locations and 3 Years :-(

    Problem is it's a finance request and they just want to data dump into Excel, arghh, but have set column order BUT also be able to select dimension members to change the data.

    I'm thinking I may just have to go with creating a new view of this data so the three sales years and one budget year are their own measures and if null/empty put 0, then create a new cube.

    I could then simply drag the columns in so always same order even if 0.

    Year2021,Budget2021,Year2020,Year2019

    J

    0
Please sign in to leave a comment.