topic Re: Expression is returning incorrect results in QlikView

Expression is returning incorrect results

Thu, 01 Aug 2013 20:10:15 GMT

Hi,

I have been trying to make a chart that shows each person in the group's open tasks, and group them by how long they have been open.

We know ia job is open becauase it doesnt have a completed date populated.

We know when the job was created to calculate the length of time open with (from today).

Here is my Expression to identify open tasks:

NumericCount (if(((ows_Task_x0020_Completed = '') and (ows_Task_x0020_Created < Today())),%Key_xml_98C49F546A18E856

))

Here is my dimension to divide into more and less than 60 days groups:

= if((Today() - ows_Task_x0020_Created >=60), 'Past 60 days', 'Less than 60 days')

Here is a shot of the data that should contribute to this graph, with Tasks with completed dates removed:

The difficulty I am having is that the chart is clearly wrong. Brandon should have 3 tasks not 4, Colin should have 7 tasks not 9 (four of Colins open tasks should not show up becuase they are scheduled for after the report is ran, 8/1/13). PLease help me figure out what I did wrong here.

Re: Expression is returning incorrect results

swuehl — Thu, 01 Aug 2013 20:48:11 GMT

Maybe the key you count is not unique in this context? Try a DISTINCT qualifier in your count function.

Re: Expression is returning incorrect results

Fri, 02 Aug 2013 18:52:29 GMT

Ok I tried a couple of things, and I keep getting the same result. I tried using the distinct option, but it didn't result in any changes. I also found a way to add the unique ID field from sharepoint to be certain I was counting a field with unique values, and pointed my count command at that. I am still getting the same incorrect result. Here's what I am currently working with for my expression:

Count ( if(((ows_Task_x0020_Completed = '') and (ows_Task_x0020_Created < Today())),ows_ID

))

Any other thoughts?

Ellen

Re: Expression is returning incorrect results

swuehl — Fri, 02 Aug 2013 20:28:16 GMT

Ellen,

any chance that you upload a small sample QVW application?