[RowNo1Check]={"Field1 = Field2"}

[cbushey1]

Has anyone heard of using [RowNo1Check] before?

Can anyone explain to me what it is doing and where I might be able to find documentation?

It would seem to allow you to compare two fields in set analysis but I only briefly saw this in a post from several years ago.

Thanks!

[swuehl]

In set analysis, there are only field names allowed to the left of the equal sign in a field set modifier, hence [RowNo1Check] needs to be a field in your data model.

The syntax would then look like

{< [RowNo1Check]={"=Field1 = Field2"} >}

Note the equal sign in front of Field1 to indicate an advanced search, which basically created a virtual table with [RowNo1Check] as dimension and

=Field1 = Field2

as expression. All dimensional values where the expression returns true will be included in the set.

edit:

Latter implies that [RowNo1Check] needs to be granular enough to allow your expression to be evaluated correctly. And if it's granularity is record-level, you can also skip the set expression with its additional overhead and go for a simple embedded if() statement:

Sum(If(Field1 = Field2, FIELD))

[cbushey1]

Thanks for the quick reply.

So lets look at a real world example.

Say I have two calendars (Summary and master). Say I want to Sum SummaryAmount but only where Summary and Master calendars are equal. Would it look like this?

Sum({<MasterYear={"=MasterYear = SummaryYear"}>}SummaryAmount)

Assume MasterYear is the year field in the master calendar and SummaryYear is the year field in the Summary calendar.

[swuehl]

How are your three fields involved in your expression related to each other in your data model?

[cbushey1]

I have many more tables than this, but this gives you the structure of how my data model is constructed.

[swuehl]

As I tried to explain, the field you are using in your set modifier needs to be granular enough to allow a proper evaluation of the field value comparison in the expression of the virtual table (to see what I mean, create a straight table with MasterYear as dimension and

=MasterYear = SummaryYear

as expression. Do you see your expression evaluated to TRUE (-1) or FALSE (0) for each dimensional line? I guess not.

But even when you are trying to use

Sum( If(MasterYear= SummaryYear, SummaryAmount))

I believe the results would be not correct. Your data model link the calendars only by AccountID, so your keys are not granular enough to support a master calendar field value to correctly filter the Account and AccountSummary tables.

[cbushey1]

I tried your straight table example and you are right, it doesnt return any values.

I agree with you that the Sum IF would not return the correct results either.

Given that my requirement is to filter down the data based on the users MasterCalendar selections of either year or month and my account level data is not currently being filtered down, what would be the best approach to get this to work? Would it be creating one Master Calendar using the Canonical Date approach HIC has outlined?

[swuehl]

It might be worth considering a different model, it's best trying to achieve a star schema with a single fact table.

It looks like you are having at least two or more fact tables (for events and revenues) which are linked by dimensional tables. If you manage to create a star schema, I think your calendar problems might be solved more easily.

Besides that, if your users selects a single year or month in the master calendar, you can try to copy the selection to the summary calendar, something like

Sum({<SummaryYear = MasterYear >} SummaryAmount)