topic Re: Dimension containing IF statement leads to wrong figures in QlikView

Dimension containing IF statement leads to wrong figures

Wed, 27 Jun 2012 11:46:46 GMT

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?

Re: Dimension containing IF statement leads to wrong figures

swuehl — Wed, 27 Jun 2012 12:19:32 GMT

Try creating two tables, one with the first calculated dimension as reference, then another with your extended version of the calculated dimension. Add another dimension to both tables that group your data into more granular sections, like 'Job' or 'JobType'.

Compare both tables, I assume that some of your jobs are not correctly classified, i.e. they are classified to MAINTENANCE but shouldn't.

Last assumption is just wild guessing since you are not providing much information about your data model, expressions etc.

Re: Dimension containing IF statement leads to wrong figures

Thu, 28 Jun 2012 06:50:02 GMT

I already did that. Didn't help. Let me explain my problem in another way.

Let's say I've got a list of jobs and their sales value, e.g.

Job SalesValue

A 50

B 100

C 200

D 100

B and D are maintenance jobs. After applying that IF statement to dimension job these jobs are perfectly aggregated.

Job SalesValue

A 50

MAIN 200

C 200 50

But for some reason job C shows only a sales value of 50, so 150 just disappeared from that job and it isn't anywhere else.

I discovered that it depends on that property to hide NULL values. If I untick that one I get another line showing minus sign where all my missing sales values are in as total figure.

Job SalesValue

A 50

MAIN 200

C 50

- 150

Re: Dimension containing IF statement leads to wrong figures

swuehl — Thu, 28 Jun 2012 11:59:00 GMT

Could you post a small sample file?

How are Job, JobType and Description related?

Do you also get a SalesValue assigned to '-' in the first case?

As far as I can see, you only get NULL returned for a given job when there is a Job Description missing for a JobType or when

(len(Aggr(sum(Amount),Job)) <= 0

[it seems that your both logical operands are identical (len(Aggr(sum(Amount),Job)) > 0 repeated), is this what you want?]

Regards,

Stefan

Re: Dimension containing IF statement leads to wrong figures

Thu, 28 Jun 2012 12:25:43 GMT

Any idea how to solve this?

I mean untick that property is not an option because I still just want to see jobs that have an impact in current selected period. Otherwise my job list would get very long.

Re: Dimension containing IF statement leads to wrong figures

Thu, 28 Jun 2012 12:39:40 GMT

Sorry, I think I'm not allowed to publish real company data.

Job# is the key, [Job Description] is in a seperate job table and JobType as well as SalesValue is in multifacts table.

Yes, of course I get that minus/dash line in first case. That's why I ticked that property to hide NULL values.

Yes, you are right it is doubled in my example. I deleted the set_expression for both because it is not needed to understand the problem. Normally it looks like:

IF((len(Aggr(sum({$<set_expression>}Amount),Job)) > 0 OR len(Aggr(sum({$<set_expression>}Amount),Job)) > 0),[Job Description])

Re: Dimension containing IF statement leads to wrong figures

Thu, 28 Jun 2012 12:42:35 GMT

But you bring me to an idea. What about lines in multifacts that have wrong or no JobType? Should not be the case, but could... I'll check that.

Re: Dimension containing IF statement leads to wrong figures

Thu, 28 Jun 2012 12:52:33 GMT

No, JobType seems to be correct.

Re: Dimension containing IF statement leads to wrong figures

swuehl — Thu, 28 Jun 2012 12:56:37 GMT

And what about the jobs that doesn't match your first line

IF((len(Aggr(sum({$<set_expression>}Amount),Job)) > 0 OR len(Aggr(sum({$<set_expression>}Amount),Job))

I think I can't really help you here, since I don't have enough information about your setting.

You don't need to post confidential company data, you could either scramble data or create a sample that replicates your issue (last should be really helpful since you need to understand your data to create a relevant sample. So at the time you can replicate the issue in a simplified sample, you'll probably already understand what is causing the issue).

As said, as a general rule I would advice to add more detailed information to your tables (dimension and / or expressions, for example JobType and JobDescription as dimension) trying to get more insight.

Re: Dimension containing IF statement leads to wrong figures

Fri, 29 Jun 2012 15:17:43 GMT

If I just use one of these two IF statements then it works. Seems to relate to the combination of both.

I don't know how to scramble data in QlikView. But I tried to create a small example (see attached). Funny thing it works.

Re: Dimension containing IF statement leads to wrong figures

swuehl — Fri, 29 Jun 2012 21:31:15 GMT

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?

Re: Dimension containing IF statement leads to wrong figures

Mon, 02 Jul 2012 15:43:21 GMT

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

Re: Dimension containing IF statement leads to wrong figures

swuehl — Mon, 02 Jul 2012 16:14:23 GMT

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

Re: Dimension containing IF statement leads to wrong figures

Mon, 02 Jul 2012 16:45:51 GMT

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...

Re: Dimension containing IF statement leads to wrong figures

Thu, 12 Jul 2012 11:22:24 GMT

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?

Re: Dimension containing IF statement leads to wrong figures

Thu, 02 Aug 2012 11:15:19 GMT

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?

Re: Dimension containing IF statement leads to wrong figures

Thu, 02 Aug 2012 11:44:26 GMT

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?