topic Re: Derived fields not working in Set Analysis in App Development

Derived fields not working in Set Analysis

stephencredmond — Thu, 26 Feb 2015 12:04:31 GMT

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

Re: Derived fields not working in Set Analysis

hic — Thu, 26 Feb 2015 12:09:20 GMT

Classified as a bug.

HIC

Re: Derived fields not working in Set Analysis

stephencredmond — Thu, 26 Feb 2015 12:34:43 GMT

😞

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

Thanks Henric.

Regards,

Stephen

Re: Derived fields not working in Set Analysis

rbecher — Thu, 26 Feb 2015 13:57:27 GMT

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

Re: Derived fields not working in Set Analysis

stephencredmond — Thu, 26 Feb 2015 14:34:18 GMT

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

Re: Derived fields not working in Set Analysis

tseebach — Tue, 17 Mar 2015 15:56:33 GMT

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.

Re: Derived fields not working in Set Analysis

tseebach — Tue, 17 Mar 2015 15:57:56 GMT

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

Re: Derived fields not working in Set Analysis

Oleg_Troyansky — Wed, 13 Jul 2016 20:31:39 GMT

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

Re: Derived fields not working in Set Analysis

plexpro52 — Wed, 14 Dec 2016 21:58:52 GMT

Hello Oleg,

Any news?

Thanks,

René Valencourt

Re: Derived fields not working in Set Analysis

rbecher — Wed, 14 Dec 2016 23:15:48 GMT

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.

Re: Derived fields not working in Set Analysis

Oleg_Troyansky — Thu, 15 Dec 2016 16:03:10 GMT

Sorry, haven't had a chance to research it. If anyone has any input, please share.

Re: Derived fields not working in Set Analysis

plexpro52 — Thu, 15 Dec 2016 16:18:36 GMT

Hello Ralf,

>> ...these fields are calculated fields, not real! ...nobody has showed me this working with 500 M rows.

Hmm, I was considering changing my set-up for calendars... Better not to change from a canonical calendar, I guess?

René

Re: Derived fields not working in Set Analysis

tseebach — Fri, 16 Dec 2016 09:27:36 GMT

I tested it on 100M rows, works pretty well! And its much faster during load.

Re: Derived fields not working in Set Analysis

rbecher — Fri, 16 Dec 2016 09:38:24 GMT

Why this should be faster in LOAD than a Master Calendar?

Re: Derived fields not working in Set Analysis

hic — Fri, 16 Dec 2016 09:41:07 GMT

Ralf

Calculated dimensions have been changed: When possible, a calculated dimension will use the code for derived fields. Which means that you CAN have selections in them. See picture below where I have a selection in a calculated dimension.

However, if a calculated dimension is based on two or more real fields, the old code for calculated fields will be used. (There may be other cases also. I haven't investigated this yet...)

Further, if the derived field is defined in the script, like in a calendar, it should be possible (and fast) to use it in Set Analysis.

HIC

Re: Derived fields not working in Set Analysis

plexpro52 — Fri, 16 Dec 2016 17:02:07 GMT

Hello Henric,

Do you know whether any examples of integrating the derived field approach with a canonical calendar, or am I misunderstanding the approach?

Thanks,

René

Re: Derived fields not working in Set Analysis

rbecher — Mon, 19 Dec 2016 07:43:21 GMT

Hi Henric,

thanks, I'm aware of that. In situations with large applications and many rows (500 M+) I have seen some unexpected single threaded processing of associations (filter on dimension table), charts with measures on two tables or calulated dimensions. Also, set analysis on high volume seems to slow down compared to a use of a 2nd dimension. Very often the only acceptable solution (from point of user experience) is having everything joined and pre-calculated in a huge single table and charts with few dimensions and measures.

- Ralf

Re: Derived fields not working in Set Analysis

hic — Mon, 19 Dec 2016 09:19:01 GMT

If you want a canonical date, you will need to create the bridge table manually. Then you can use a derived field for the date in the bridge table.

HIC

Re: Derived fields not working in Set Analysis

hic — Mon, 19 Dec 2016 09:24:45 GMT

If you have very large applications, the single threaded phase of a chart evaluation can indeed take a considerable time. And then, a solution is to join everything into one fact table.

However, this problem exists irrespective of whether you use set analysis (or derived fields) or not. So, I don't think that it gets worse because you use derived fields.

HIC

Re: Derived fields not working in Set Analysis

Oleg_Troyansky — Mon, 19 Dec 2016 23:57:31 GMT

I finally got around to testing the Derived Fields... Some interesting results:

- Comparing an app with a derived calendar, with a "conventional" app with a regular Master Calendar - the two applications ended up with precisely the same size on disk, which tells me that Derived fields are stored in memory just like the regular fields (originally, I thought that they are "virtual", not stored)

- Using Derived calendar fields compared to "conventional" calendar fields - getting the same performance for the most part.

- However, in many instances, Qlik Sense seems to "freeze" and enter into a long (possibly infinite) calculation phase when trying to build lists of derived values. It happens in several situations:

- Adding a derived field into a Filter Pane

- Adding a derived field as a Chart Dimension

- Opening a "Selection Tool" and scrolling to the right to show all derived Calendar fields

After this problem happens for the first time, several random things stop working, and I usually have to close the App and start all over again. I suspect it's a bug that needs to be looked into.

Tested in Qlik Sense Desktop, ver. 3.1 SR3.

cheers everyone!

Oleg Troyansky

Did you know that Masters Summit for Qlik is coming soon to Munich, Germany?