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]

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.

[#3]

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

[swuehl]

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

[#5]

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.

[#6]

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])

[#7]

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.

[#8]

No, JobType seems to be correct.

[swuehl]

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.

[#10]

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.