Did you try creating a field that contains the following formula
and use it as a filter and just keep True.
You will need to tweak the filter table calculation's settings to get the right 1 to keep. In this case, if you get this view because aggregate measures is turned off, then you will need to put both week and year into partitioning box.
Bora Beran, Thanks for the quick answer,
I don't entirely understand your step after I have created the new calculation field with the formula "INDEX()=1" and filtered it with TRUE.
Can you please explain it a little further?
Thanks in advance
all the pills with the triangle icon are table calculations. if you right click on the pill, you will see an option named "edit table calculation".
If you click that you will get a pop up dialog. Go to compute using drop down, select advanced... and in the next dialog move both year and week to the box that says partitioning. Based on your screenshot my guess is that this setting should work. If not, play around a bit to get the expected results.
INDEX will count the items in each partition. If you set each partition to whatever is the box of repeating value, it will only keep one of those values and hide the rest.
Bora Beran The calculations are already like that as can be seen by these pictures:
As ROI TV is depended of Transf TV, it is part of the Table Calculation.
If I change any of the Calculation Definitions, it will change the calculations (Which are correct at the moment)
you don't need to change definitions of any of these.
INDEX()=1 is the calculation I was referring to for these settings. Not the ones you already have.
When you use INDEX()=1 as a filter, it won't change your calculation results. It will just hide the marks as table calculation filters apply after table calculations compute.
Perfect! Thank you very much for your help!
Bora Beran is correct. None of the calculations will change. When you created your calculated fields what you've done is for the entire table, or for the entire array if you prefer excel terms you've returned the same value. Now each row in each partition of your database has the same value. What INDEX() does its counts which item something is within a given partition. If it's in the 1st one, the 2nd one, the 35th one etc.
Because you have the same value for all of these rows within each partition you can set a new calculated field and a filter INDEX() = 1. All this is doing is giving a response true or false for every row in the partition. So when its the first item it will return true and all the others will be false. The filter set's it up where you only return true for everything. You may then need to set index() =1 calculated field up so that it is not using a default table calculation. May need to play with it a bit. Without seeing the data I can only guess how to set it up.