Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Finally got around to writing that blog post:
Q-Tip #16 – What’s Special About Those Derived Fields | Natural Synergies
enjoy!
Oleg Troyansky
Ohh, HIC, while your at could you also look at why derived fields are not working in URL parameter selections?
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?
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)
Dear Henric,
In the Sept 2017 version this bug is gone, i hope!
Could you please confirm this???
thanks a lot,
nice regards, Emma
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
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é
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
Got it, thanks!
Thanks a lot Henric, I did not pay attention to Dual character of auto calendar.