Derived fields not working in Set Analysis

[stephencredmond]

Hi,

I have been playing with Declare and Derive.  I have created a calendar using the script from the help however, when I use the field in Set Analysis, I get no limitation.  So the expression:

Sum({<[OrderDate.Calendar.Year]={2013}>}[#Sales Total])

Will calculate across all available values, as if the set was not there.

In the same model, I have also created a Year field from the same OrderDate field - in the "old fashioned" way.  The expression:

Sum({<[Year]={2013}>}[#Sales Total])

Correctly calculates for only values in 2013.

If I juxtapose both year fields on the layout, selections in one field are correctly reflected in the other field.

Are derived fields not supported for Set Analysis?

Regards,

Stephen

[hic · Accepted solution]

Classified as a bug.

HIC

[stephencredmond]

😞

I was hoping that it was something that I was doing wrong.

Thanks Henric.

Regards,

Stephen

[rbecher]

What if you define a dimension? The derived field itself seems to be invisible.

[stephencredmond]

Hi Ralf,

I can define a dimension from the derived field and can use that as a List Box or in a Chart, but that doesn't work in a Set - I am not sure if it is supposed to?

Regards,

Stephen

[tseebach]

Guys, there also is some of other usual stuff that does not work with the Derived fields.

Take this Set Expression:

Sum({<

[PostingDate]={">=$(=Addmonths(Max([PostingDate]),-12))"}*{"<$(=Addmonths(Max([PostingDate]),0))"}

, [PostingDate.C.Month]=,[PostingDate.C.Year]=,[PostingDate.C.Week]=,[PostingDate.C.Quarter]=

>} SalesAmount )

The Fields that I'm trying to disregard and override are not being cleared. So my chart looks like this:

The last 10 months of 2014 are missing.

[tseebach]

And I'm also not able to do any calculations on the derived fields. Like AddMonths() or YearStart().

[Oleg_Troyansky]

Hi everybody,

I'm really-really late to this party, but I just experienced something similar recently, and I'd like to comment...

What could have been a real bug in early 2015 (I assume it's fixed by now), may still be a problem, only of a different kind...

Transition to derived fields requires a slightly different approach to using these fields in set analysis. The difference is the most evident with the field Year.

Traditionally, we calculated Year as a numeric field in our Master Calendar:

Year(Date) as Year

The default derived field Year is not calculated as a Dual field:

Dual(Year($1), YearStart($1)) as Year   (I'm quoting from memory).

So, the same numeric filters as we always used for the "traditional" field Year, will not work with the derived field Year. Instead, we should use Advanced Search and apply the Year() function there in order to enable numeric comparison.

I'll try to research this topic a bit more and post a blog about it.

just wanted to add my $0.02 to this topic...

cheers.

Oleg Troyansky

[plexpro52]

Hello Oleg,

Any news?

Thanks,

René Valencourt

[rbecher]

Well, guys, watch out these fields are calculated fields, not real!

Although, this can be probably used with loading from some Excel Sheets but not with real data.. At least nobody has showed me this working with 500 M rows.