Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stephencredmond
Luminary Alumni
Luminary Alumni

Derived fields not working in Set Analysis

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

34 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

plexpro52
Creator
Creator

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é

tseebach
Luminary Alumni
Luminary Alumni

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
hic
Former Employee
Former Employee

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.

Derived field.png

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

plexpro52
Creator
Creator

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é

rbecher
MVP
MVP

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

Astrato.io Head of R&D
hic
Former Employee
Former Employee

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

hic
Former Employee
Former Employee

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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?