Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

caio_caminoski
Contributor

NULL FIELD PROBLEM IN SET ANALYSIS

Hello community,

I have a metric that uses a set analysis (  {<FIELD ={'Y'}>}  ) . My problem starts when all the values of my 'FIELD' are null. Qlik completely ignores that null is different than 'Y' and don't even bother evaluating the set analysis, giving me the total sum of the value field instead of 0.

For example, in the script:

TABLE:

Load

RowNo() AS CODE

    ,Null() AS DIM

    ,1 AS VALUE

AutoGenerate 15;

In the KPI:

Sum( {< DIM = {'Y'} >} VALUE)

As there is no data 'Y' in the field DIM i should be getting 0, but instead I am getting 15.

Captura.PNG

Any ideas?

1 Solution

Accepted Solutions

Re: NULL FIELD PROBLEM IN SET ANALYSIS

May be it seems as bug.

As a work around try this.

Sum( {<CODE -= {"=isnull(DIM)"},DIM = {"Y"} >} VALUE)

Regards,

Kaushik Solanki

9 Replies

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi,

Add two variable as shown below.

NullAsValue *;

Set NullValue = 'NULL';

TABLE:

Load

RowNo() AS CODE

    ,Null() AS DIM

    ,1 AS VALUE

AutoGenerate 15;

Now it wont show 15.

Regards,

Kaushik Solanki

caio_caminoski
Contributor

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi Kaushik,

Thank you for your answer. However, we really didn't want to transform null into values. Do you think there is another way to get the set analysis to work with the null values?

This behavior looks like a bug, right?

Re: NULL FIELD PROBLEM IN SET ANALYSIS

May be it seems as bug.

As a work around try this.

Sum( {<CODE -= {"=isnull(DIM)"},DIM = {"Y"} >} VALUE)

Regards,

Kaushik Solanki

mikofme
New Contributor

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi Caio,

it's not a bug. Keep in mind that using SetAnalysis is about the same a selecting values in a listbox. In your LB of field DIM there is no value 'Y' avaliable, so clicking on 'Y' by SetAnalysis just doesn't have any effect - the same as in the real LB.

Add some dummy record to your table, like this:

     TABLE:

     LOAD * INLINE [

         CODE,DIM,VALUE

         ,Y,

     ];

and the SetAnalysis statement will be able to select it.

caio_caminoski
Contributor

Re: NULL FIELD PROBLEM IN SET ANALYSIS

That is a good one! Thank you Kaushik.

At the end I saw that your solution works for the exemple I gave, but in my real table I don't have a unique identifier that I can use as you did in the function.

So I am back to where we were.

Anyway thank you very much again.

Caio

caio_caminoski
Contributor

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi Michael, thank you for your answer,

The idea you are suggesting we also took into consideration, however our data model is complex. We don't want to be concatenating rows or adding inlines "just in case".

I understand what you mean, but I see set analysis as "forced pre-selected filters”, so if I force a field to evaluate my sum only for DIM = ‘Y’ and there is no ‘Y’, my value has to be 0, right?

This is a normal behaviour for a set analysis when there is at least one register not null. If I have a field with ‘Apples’ and ‘Bananas’ and I use in my set analysis ‘Oranges’ the result is zero because there is no match for my pre-set filter.

But thanks again for the suggestion.

Regards,

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi,

Can you tell us what u have in your real scenario.

Regards,

Kaushik Solanki

caio_caminoski
Contributor

Re: NULL FIELD PROBLEM IN SET ANALYSIS

Hi Kaushik,

Sure! Basically we have a lot of data from different countries. As we have millions of rows, we don't have a field for unique identifiers (such as RowIDs) as the size of the file grows too much.

Then we have a KPI with an expression as it follows:

Sum( {< _Flag = {'Y'} >} Value ) / Sum( {< _Flag =  >} Value)

The problem started when we published the app in one stream and restricted the data only to a specific country. By chance it happened that for that specific country all the registers in the field ‘_Flag’ were null. Once it is null, Qlik did not evaluate the set analysis and it is giving 100% instead of 0%.

I am not sure if I was clear. Let me know if it makes sense!


All the best,

Caio

Re: NULL FIELD PROBLEM IN SET ANALYSIS

It does make sense. Let me try couple of codes and come back to you.

Regards,

Kaushik Solanki

Community Browser