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

[Oleg_Troyansky]

Finally got around to writing that blog post:

Q-Tip #16 – What’s Special About Those Derived Fields | Natural Synergies

enjoy!

Oleg Troyansky

[tseebach]

Ohh, HIC, while your at could you also look at why derived fields are not working in URL parameter selections?

URL Selection on Derived Calendar Fields

[Anonymous]

Hi, is that bug still unsolved?

I found the same problem here accidentally: Set analysis in pivot table

(using Qlik Sense Desktop 3.2 R2)

Neither getselectedcount() nor set analysis work for the derived [YearMonth] fields.

Perhaps the declaration is not correct for that example?

[ersanduran]

Hi,

It seems the bug is only partially solved. Derived calendar fields still do not work correctly. If Year from auto calendar is used together with simple equation it works fine, however if a search within Year values is performed it will not work.

For example:

This works: Sum({<Year={2017}>} Sales)

This does not work: Sum({<Year={">2016"}>} Sales)

[EmmaC]

Dear Henric,

In the Sept 2017 version this bug is gone, i hope!

Could you please confirm this???

thanks a lot,

nice regards, Emma

[hic]

I just tested it, and I can confirm that the bug is gone in the September release.

The problem that ersan.duran describes is however a different one:

      Sum({<Year={2017}>} Sales)

will compare the textual part of the dual value, whereas

      Sum({<Year={">2016"}>} Sales)

will compare the numerical part of the dual. Normally, there is no difference between the two, but if you have defined your Year using the YearStart() function, which the autocalendar does, it will make a difference.

So, for

      Dual(Year($1), YearStart($1)) AS [Year],

you need to use

      {<Year={">2016-01-01"}>}

but for

      Year($1) AS [Year],

you need to use

      {<Year={">2016"}>}

HIC

[plexpro52]

Hello Henric,

>> I can confirm that the bug is gone in the September release.  The problem that Ersan Duran describes is however a different one...

So, is the instruction you gave -- for Year($1) AS [Year], you need to use {<Year={">2016"}>} -- a workaround for which a fix will be forthcoming, or the optimal way to handle this?

René

[hic]

The behaviour will not change.

The numerical value for YearStart() of 2017-01-01 is 42370, and the greater-than-comparison should of course use this number if the Year field is defined using YearStart().

HIC  

[plexpro52]

Got it, thanks!

[ersanduran]

Thanks a lot Henric, I did not pay attention to Dual character of auto calendar.