topic Same filter, two objects, twice the results in QlikView

Same filter, two objects, twice the results

Fri, 09 Dec 2011 21:27:05 GMT

Hi,

I have a straight table with a pretty complicated IF statement as one of the dimensions. It works fine.

When I use the same if statement in a bar chart as an expression: SUM ( IF (blah AND blah AND blah, 1, 0), I get exactly twice as many items. I have a hack where I can do SUM ( IF (blah AND blah AND blah, .5 , 0)... but it seems there's got to be a proper way.

It's 2x as many in charts with both a straight-up field dimension and a calculated dimension.

Any thoughts?

Thanks,

Same filter, two objects, twice the results

swuehl — Fri, 09 Dec 2011 23:47:00 GMT

Could you tell us a little bit about your data model, or would it be possible that you upload a small sample app that demonstrate your issue?

Re: Same filter, two objects, twice the results

Mon, 12 Dec 2011 23:07:19 GMT

These are copied verbatim from the object's properties, except changing any identifying field names. I pretty much copied from one expression/dimension to the other when I created the objects.

Table definitions

First Dimension in table:

=if( (workflow = 'Special' or workflow = 'Normal' or IsNull(workflow))

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state<>'Draft')
, Change_number)

Second Dimension in table:

=if(timing = 'Emergency','Emergency',
if(expedited_date >0, 'Expedited', timing) )

I’m not using any expressions.

Then I supress all where value =null, because I want to show only the items where all those are true.

Bar Chart definitions

First Dimension in the chart:

=if(timing = 'Emergency','Emergency',

if (expedited_date > 0,'Expedited', timing) )

First expression in the chart:

count( if (
(workflow = 'Special' or workflow = 'Normal' or IsNull(workflow) )

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state <>'Draft')
, ' ' ) )

This is how I solved it, effectively halving the count:

sum ( if (
(workflow = 'Special' or workflow = 'Normal' or IsNull(workflow) )

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state <>'Draft')
, .5, 0 ) )

Please let me know if you have any insight.

Thanks again for your help!

Re: Same filter, two objects, twice the results

rohit214 — Tue, 13 Dec 2011 05:34:02 GMT

try this

sum ( if (
(workflow = 'Special' or workflow = 'Normal' or IsNull(workflow)='-1' ,

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state <>'Draft')
, .5, 0 ) ) )

mat it help you

thanks

rohit

Re: Same filter, two objects, twice the results

Tue, 13 Dec 2011 15:03:39 GMT

Thanks Rohit, but that didn't change anything.

I'm ok with the hack of summing 1/2 of every record. Maybe that is the solution - it just seems weird to me.

Same filter, two objects, twice the results

swuehl — Tue, 13 Dec 2011 16:54:44 GMT

Hm,

I would be cautious to just live with summing up 1/2 of every record, unless you understood why you need to to this. The factor could potentially change if you only slightly change your data model or load different data.

You've said you are not using any expression in your first table, not even a constant, hidden '1'? I just don't get how you do see anything them, my tables always show nothing without an expression.

Then, is there a special reason why you count ' ' (looks like a space) in your second table, and not e.g. ChangeNumber?

Could you try:

count( if (
(workflow = 'Special' or workflow = 'Normal' or IsNull(workflow) )

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state <>'Draft')
, ChangeNumber ) )

and

count(DISTINCT if (
(workflow = 'Special' or workflow = 'Normal' or IsNull(workflow) )

and (approval = 'Approved')

and ( (DateApproved > SchedStart) OR (DateApproved > ActualStart) )

and (state<>'Canceled' and state <>'Draft')
, ChangeNumber ) )

As said, I am still unsure how your first table works without any expression, but if you compare just the number of ChangeNumber lines shown in the first table with the count of e.g. ChangeNumber in the second, this could differ, because the first will only show uniqe combinations of your two dimensions, while a count may also count non distinct values (that's why I ask you to try a DISTINCT in above expression). Not sure if I make myself clear...

Also, I am not sure if your data model might play a role here or not, could you tell us a bit more about the tables you use in this chart (maybe just copy a screenshot of your table viewer here)?

At best, you could upload a small sample qvw file here to the forum, that demonstrates your issue.

Regards,

Stefan

Same filter, two objects, twice the results

cesaraccardi — Wed, 14 Dec 2011 14:22:00 GMT

Hi! I see you are using these in the dimensions, could you transfer to the script? I think it will be easier to work with the charts and also with better performance.

Regards,
Cesar.

Same filter, two objects, twice the results

Wed, 14 Dec 2011 15:42:53 GMT

Hi Cesar,

I tried to create a new field to flag all the records I want - that is, is each record late, approved, active, etc, (also using a join) and could not work through how to combine all those Ifs into one step in the script. I am just more comfortable with dimensions and expressions for now.

I might try that in the next iteration of improvements.