Hiding rows and Lines

[JamGardner]

Hi All, 

hoping someone can help with what will probably be very simple, but I am struggling with it. 

I have a set of pivot tables and vizlib line charts that I have created where I use a drill down dimension going; 

Top Level > level 2 > sub unit (location) 

I have applied some set analysis to the measure on each of the visualisations to narrow down the data displayed; 

sum({<CustType -={'Business A'},Location-={'Unknown'},Level2={'Sales'},Date={">=$(=(MonthStart(Max(Date)-182)))<$(=MonthStart(Max(Date)))"}>}AgentSales)
/
Sum({<CustType -={'Business'},Location-={'Unknown'},Level2={'Sales'},Date={">=$(=(MonthStart(Max(Date)-182)))<$(=MonthStart(Max(Date))))"}>}Contacts)

Which all works fine and gives me the information I want to display; what I want to do is tidy up the data displayed so that it only shows the locations where we have had 100+contacts in the latest week to be displayed as lower than that doesn't impact the overall results to a great extent. 

I have tried adding Contacts={'>=100'} as another element to the set analysis however it caused the table not to display any data. 

Can anyone drop me a line on the best way to essentially remove/hide the locations with low contacts based on the latest weeks results 

Thanks

[abhijitnalekar · Accepted solution]

Hi @JamGardner ,

Please Try below.

create a new dimension for location as

if(

Sum({<CustType -={'Business'},Location-={'Unknown'},Level2={'Sales'},Date={">=$(=(MonthStart(Max(Date)-182)))<$(=MonthStart(Max(Date))))"}>}Contacts)>100,

DimensionName) 

Hope this helps you

[JamGardner]

Hi @abhijitnalekar 

Thanks for coming back to me, I wasn't quite sure I followed with the above. Are you meaning create a dimension in my load script? Sorry if this is a basic question. 

[abhijitnalekar]

Hi,

create a variable at the application level. and use the same in pivot table