Dimension containing IF statement leads to wrong figures

[#1]

Hi @all,

I've got a list of all jobs that have an impact in current selected period. It's in a table chart and the only dimension is description of the job.

IF((len(Aggr(sum(Amount),Job)) > 0 OR len(Aggr(sum(Amount),Job)) > 0),[Job Description])

I also ticked that property to hide NULL values and that works pretty fine.

Now I'm trying to aggregate maintenance jobs. So, I changed dimension to

IF((len(Aggr(sum(Amount),Job)) > 0 OR len(Aggr(sum(Amount),Job)) > 0),

  If(not IsNull(JobType) AND ((Right(JobType,3) = 'XXX') OR (Right(JobType,3) = 'YYY')),
    'MAINTENANCE',

    [Job Description]))

Now all maintenance jobs are aggregated to one line, that's fine. But some of the figures of other jobs also changed.

Any idea why this happens?

[swuehl]

You can scramble data in Settings - Document properties - Scrambling.

But I think you are almost there. You just need to find the relevant difference in your data model in the sample file.

How do your set expressions in the first if-statement look like? How are the fields you are using in the set expressions related to your job tables?

[#3]

I added set expression and it still works fine. I don't see any difference to my real dashboard from table relation perspective.

[swuehl]

You are using only one set expression, AFAIR, you mentioned that the problem starts when using a combination of two?

[#5]

No, that issue doesn't start when using two set expressions. It starts when using two IF statements in dimension.

Two set expressions are only used to get figures from two different account types. So, it would also be possible to use

IF((len(Aggr(sum({$<AccountType={'P','M'}>}Amount),Job)) > 0...

[#6]

Update to 10 SR5 doesn't solve that issue either.

I've seen two interesting bug fixes in SR5:

29454

Null values sorting on Dimension may result in different values in 32 and 64 bit environments

27272

If first expression in chart with multiple expressions equals NULL - No data to display

Is it possible that my issue is related to something similar?

[#7]

What about this one?

37948

Difference in calculation in 32-bit vs 64-bit with set expression and dynamic variable

I mean does anybody know what exactly is different? Is it wrong in x32, in x64 or are both wrong? Has this been solved already and what did QlikTech do to solve it?

[#8]

It's even worth after update. We use this calulated dimension in combination with that "Supress when value is Null" tick to hide jobs that don't have any impact on certain accounts within the selected period. In QV 10 SR2 we had one line where we could see an aggregation of all values without any job relation. In QV 10 SR5 this line disappeared.

My idea is, but I'm not sure and would be happy if anybody can confirm this, that we have two different types of Null values.

First one is coming from the calculated dimension. That means we use an IF statement without an ELSE statement. Every line where the condition is not true will have an empty (Null) dimension. These were hidden by that "Supress when value is Null" tick in QV 10 SR2.

Second one is coming form the core system. That means there are certain records in source system where the dimension is already empty (Null). That means even if the condition of the IF statement is true the dimension will be empty (Null) anyway. In QV 10 SR2 these records were aggregated to one line and were not hidden/supressed by that tick. In QV 10 SR5 this aggregated line disappeared from the list.

That means I assume QlikView hides/supresses only calculated Null dimensions in SR2 and now hides/supresses also source system Null dimension in SR5. Can anybody confirm this behaviour and/or share some experiences regarding this issue?