Hi,
I've been using filter to filter data in my view but when I do a calculation with the filtered dimension in a table, the value calculated seems to be taking into account the value of the filtered elements as well? Can I fix this or do I have to create a new dimension with different structure in order to get correct numbers?
This is an excellent question, thank you Nicolas!
There actually are 2 ways to filter data in Pigment. One is via the filter symbol in the view of an object (see screenshot attached) and the other way is by using the FILTER function in the formula bar.
The difference between these two is following:
By using the filter symbol in the view we are filtering the view only (we could call it a superficial level of data)
By using the FILTER function we are filtering the structure of the object in question. Imagine an example: Revenue eFILTER: Country = Country."FR"]. So here, whenever I will want to use Revenue in my Income Statement table, Pigment will be taking into account only values related to France regardless of how many other countries we had in our original Country dimension structure.
This second option allows for great modelling flexibility as you can quickly do ad hoc tests and model as you go! Also no worries about losing filtered values - you’ll always be able to take the FILTER function out and get back all of your initial data :)
So the answer is no, you don't need to create a new dimension each time you want to play around with the data in your view and it’s structure! You can just use the FILTER function :)
Please take a look at this article here that will give you a more-detailed insight into how the FILTER function works and provide some syntax examples.
Let me know if you have any other questions regarding this!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.