How to choose certain value to be shown on table columns ?

[inventlogistic2016]

So I have this master data of distribution and branch ^. And I'm trying to build a new table with only cabang (branch) data like this picture below:

What I'm trying to do is I only want show the branch_name which is comes or inherited from distribution head 1. How to create an expression that can fulfill this condition ? Because I think we can't use WHERE statement like in SQL. Any answers appreciated :), thanks.

[pedrobergo · Accepted solution]

Hi @inventlogistic2016 

The best way is using Set Analysis on each measure:

- You have formula on column UTILITY REAL like SUM(UTILITY_REAL), just add the filtering on top of that, changing it to

{<branch_name={'DISTRIBUTION HEAD 1'} SUM(UTILITY_REAL)

OR

- Inside of formula (only if you using older Sense versions)

SUM({<branch_name={'DISTRIBUTION HEAD 1'} UTILITY_REAL)

Repeat it for each column / formula on your table, then only head 1 will be showed.

[],

Pedro

[inventlogistic2016]

Hi Pedro,

Thanks for your answer.

I have tried using Set Analysis following your suggestion like this:

I've also used the set analysis to each column, but it seems the branch that is not from distribution area 1 still included in the table:

So now, I think I need to find a way how to exclude the null values, If I remember it correctly, usually there was a check / uncheck option to include null value, but now I can't see it somehow...but thanks anyway for your suggestion.

[pedrobergo]

Hi @inventlogistic2016 

To remove zero / null values from straight table, uncheck Add-ons --> Data handling --> Include zero values.

But since that you´re using older formula way, you´ll ensure all formulas contain data filter.

[],

Pedro