Ignore dimension in formula but just show specific values of dimension in chart

[richters]

Hi all,

I have got a structure like: group - codes. So each code has one group and each group can have a lot of codes.

What I want to show in a chart is every code that belongs to a selected code but in formula I have to ignore the group.

That means I have got a formula like this:

if(sum({<year={'2012'}, group=>} count_code)<sum({<year={'2013'}, group>} count_code),1,0)

So I exclude the group from the sum. I need to do this because codes may change the group it belongs to during the years but the code means the same. So it is not a new code (that is what I want to show though).

My problem is that when I have got the codes as a dimension now, It shows all codes - even if I select a group. I know, this is because I excluded it in formula but is there a trick to show just the codes that belongs to the selected group and exclude the selected group from the sum?

Thank you!

[swuehl]

Maybe like

if(sum({<year={'2012'}, code=p(), group=>} count_code)<sum({<year={'2013'}, code= p(), group=>} count_code),1,0)

It seems that you are handling a SCD, where relation code - group change over time. How do you handle this?

[richters]

What do you mean with SCD?

Well, this p() is awsome...can you explain what it does?

[swuehl · Accepted solution]

SCD is a slowly changing dimension

http://en.wikipedia.org/wiki/Slowly_changing_dimension

p() returns a list of possible values in a set expression. So we modify the code selection by assigning the set of possible values (the set that is limited by your selection in group).

Do you get a reasonably result?

[richters]

Definitely makes sense! Thank you.

Because my codes just change once a year, I just need to put it in another group. And because we just need to look at 2 years in the application, there is no double-change or anything like that. That makes it easier, doesn't it?! The good thing is, looking at these codes two years ago doesn't give us any helpful information for today.