NULL FIELD PROBLEM IN SET ANALYSIS

[caio_caminoski]

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.

Any ideas?

[kaushiknsolanki]

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]

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?

[kaushiknsolanki · Accepted solution]

May be it seems as bug.

As a work around try this.

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

Regards,

Kaushik Solanki

[mikofme]

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]

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]

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,

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[caio_caminoski]

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

[kaushiknsolanki]

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

Regards,

Kaushik Solanki