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: 
caio_caminoski
Creator
Creator

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

May be it seems as bug.

As a work around try this.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

14 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
caio_caminoski
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

May be it seems as bug.

As a work around try this.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mikofme
Partner - Contributor
Partner - Contributor

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
caio_caminoski
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!