Replace one time dimension by a second time dimension

[TomBond77]

Hi experts

We have two tables linked by dimension cost center ID in the model.

Both tables have a dimension Year-Month but with different dimension labels: YearMonth1 and YearMonth2.

YearMonth1 is used as filter pane in the app. How can you replace the value of YearMonth1 with YearMonth2 if defining a KPI based on YearMonth2?

Thank you, tom

[Oleg_Troyansky]

Hi Tom,

Ideally, the best scenario would be to model the data in such a way that you'd end up with a single YearMonth field. However, I realize that sometimes it's easier said than done.

For these cases, you could use Set Analysis, I presume - something like this:

sum({<YearMonth2=P(YearMonth1), YearMonth1= >}  Value)

This formula selects all possible values of YearMonth1 in the field YearMonth2, and then disregards selections in YearMonth1.

To learn more advanced development techniques, including advanced Set Analysis, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[TomBond77]

Thanks Oleg

What does the "P" mean in your expression?

YearMonth2=P(YearMonth1)

Regards, tom

[Oleg_Troyansky]

Hi Tom,

P() is a function in Set Analysis and allows you to select Possible values in a field. It's part of the advanced Set Analysis syntax.

I'm teaching a half-day session on advanced Set Analysis and AGGR() at the Masters Summit for Qlik. We will be in Dublin in October, and in Orlando in November. If you can attend, you'd learn lots and lots of advanced development techniques of this kind.

Cheers,