1 2 Previous Next 20 Replies Latest reply on Jun 8, 2016 5:02 AM by Bassem Khalil

# Table calculation \ filter problem

Hello,

I have a situation where I have a table on Tableau that looks like the one here in below .

We would like to show it differently.

So there is two dimensions ALL and STORE 1.

The ALL has the ALL-sales value and we have STORE 1, that has only STORE 1 sales.

And if we create a dashboard where we want to show only STORE 1 values for the area where there is STORE 1 values, and show ALL value to the AREA that does not have STORE 1 value, how it can be done?

I have tried some solutions, but none of them does not work..

The closest yet, is something like this where I try to create some kinda filter ..

IF [STORE]<>"ALL" THEN { FIXED : MAX(IF [STORE] = "ALL" THEN 0 ELSE 1 END) }

END

 COMPANY AREA 1 ALL 20 AREA 2 ALL 20 STORE 1 10 AREA 3 ALL 20 AREA 4 ALL 20 STORE 1 10

If any ideas, it would be much appreciated !

So this is what I try to achieve:

 COMPANY AREA 1 ALL 20 AREA 2 STORE 1 10 AREA 3 ALL 20 AREA 4 STORE 1 10

Cheers,

Niko

• ###### 1. Re: Table calculation \ filter problem

Hello Niko,

Hope all is well.

Check out the attached workbook and see if that solves your request.

Regards,

B

2 of 2 people found this helpful
• ###### 2. Re: Table calculation \ filter problem

Hello Bassem,

The workbook seems to have the solution that I needed!

I knew that it needed some LOD calculation, but was not sure what. I have not been so familiar with them yet..

Been using QlikView for a while and there is that Set Analysis that resembles LOD quite a bit

But, thank you a lot!

I have to try to use this on my own workbook tomorrow.

Cheers,

Niko

• ###### 3. Re: Table calculation \ filter problem

You are most welcome!

B

• ###### 4. Re: Table calculation \ filter problem

Hi Niko,

Please find the solution I have created calculation 1

Which have this true / false condition : I have used count distinct ( if you have more storename like store    1, store 2 or sometime if you more lines within company and area. also I have add company in fixed if you got more company and area etc..

Calculation1:

[Store Name]=

IF { FIXED [Company Name],[Area]:COUNTD([Store Name])}>1

THEN 'STORE 1'

ELSE 'ALL'

END

calculation 2 - for display

IF { FIXED [Company Name],[Area]:COUNTD([Store Name])}>1

THEN 'STORE 1'

ELSE 'ALL'

END

you can try ..instead of above if you are looking to solve store 2 , store 3 if they are in line and you want to display those

{ FIXED [Company Name],[Area]:MAX([Store Name])}

1 of 1 people found this helpful
• ###### 5. Re: Table calculation \ filter problem

Hello Sanjay,

I will take a look on to this solution as well!

Thank you for your input, it seems to be a good solution as well

Cheers,

Niko

• ###### 6. Re: Table calculation \ filter problem

Hello B,

For some reason, I get False for all values when I am trying to implement this to my workbook :/

It is resembled some like this table here below:

 Company1 Area1 Store1 False 10 Company2 Area2 Store2 False 15 Company2 Area3 Store2 False 15 Area4 Store2 False 15 Company3 Area5 Store1 False 10 Area5 Store3 False 20

So in Company 3, we would have to decide which value to show.

They are in the Same Area5, so we would like to show the value from Store1 instead of overlapping these two values when hiding the Store dimension.

And we do not want the avg or sum between these two, just the value from Store1.

And I am confused, that why did that what you suggested before, did not work :/

Cheers,

Niko

• ###### 7. Re: Table calculation \ filter problem

Hi,

When I put this {EXCLUDE [Store Name]: count([Store Name])} on the row shelf, I get values 1 and 2 for every Store name.

It is not somehow doing it right :/

I think I am missing that calc along -option.. How that can be managed, if I am not using any table calculations on the field ?

Cheers,

Niko

• ###### 8. Re: Table calculation \ filter problem

Hey Niko,

What I meant with "calc along" in the workbook was meant to be "use the calculation 'and' the area field".

Sorry for the confusion .

Check the animation for the steps. Hope this helps!

• ###### 9. Re: Table calculation \ filter problem

Hi,

Thank you for all your help, I am very pleased to see that someone is trying to even help !

But I think this problem is not solved yet .. When I add measures or dimensions to the text marks, it shows two rows if the measure is not the same value for both rows.

My problem is like here in below..

Cheers,

Niko

• ###### 10. Re: Table calculation \ filter problem

No worries Niko!

Would you be able to provide a workbook (.twbx) so I can see what is happening?

Best,

B

• ###### 11. Re: Table calculation \ filter problem

Hello,

It is sensitive data.. I have to create some kinda sample file for this.

And I forget to red line the ones that have same "area"

Niko

• ###### 12. Re: Table calculation \ filter problem

Okay so let's try and continue without a sample.

Could you right click and exclude the Null values and see what pans out?

B

• ###### 13. Re: Table calculation \ filter problem

Hello,

I created a dummy file to resemble my problem. Hopefully it helps

Cheers,

Niko

• ###### 14. Re: Table calculation \ filter problem

There is a wrong stuff in Store Name - With Null for ALL

ELSEIF [Store Name]= "STORE 1"

should be in this case Store 1 ...

But does not help for my problem

1 2 Previous Next