topic Re: Expression error? in QlikView

Expression error?

Tue, 11 Mar 2014 09:07:19 GMT

Hi,

I have dimension and expression as below in straight table

Dimension: TeamMember

Expression: Count(DISTINCT {$<Source={'ITDepartment'},Order={ITDepartment'},Result={'Work Completed'},Flag={1}>}EmployeeID)

when I select the Date from 03/03/2014 to 07/03/2014 the count of the expression is showing 10 whereas in the database the count is showing 5. can anynone please tell me is there anything wrong in expression?

Thanks.

Re: Expression error?

sudeepkm — Tue, 11 Mar 2014 09:12:16 GMT

I see there is a single quote missing in the below expression: Order={ITDepartment'}

Can you please verify it? Or please post a sample QVW.

Expression: Count(DISTINCT {$<Source={'ITDepartment'},Order={ITDepartment'},Result={'Work Completed'},Flag={1}>}EmployeeID)

Re: Expression error?

Tue, 11 Mar 2014 09:14:04 GMT

Thanks. sorry it is a typo mistake. in the file i haven't missed anything.

Re: Expression error?

MK_QSL — Tue, 11 Mar 2014 09:20:40 GMT

Count({$<

Source = {'ITDepartment'},

Order = {'ITDepartment'},

Result = {'Work Completed'},

Flag = {1}

>}DISTINCT EmployeeID)

I don't think so this will affect your end result. But try it at least once.

OK... let us have your sample apps.

Re: Expression error?

er_mohit — Tue, 11 Mar 2014 09:22:42 GMT

Try to change expression like this

make sure Date Format is same .

Count( {$<DateField={'>=$(=Date(Min(Datefield)))<=$(=Date(Max(Datefield)))'},Source={'ITDepartment'},Order={ITDepartment'},Result={'Work Completed'},Flag={1}>}EmployeeID)

Re: Expression error?

sudeepkm — Tue, 11 Mar 2014 09:26:11 GMT

That's strange because if you are saying that in your database table the count returns 5 EMP IDs and in QlikView even after you do distinct the count is 10.

I think it would be easy if you can post the QVW. The expression looks fine to me. I need to know how the table is created in QlikView after you loaded it from DB.

Re: Expression error?

Tue, 11 Mar 2014 09:26:21 GMT

Please see attached as this is sample data.so, it is showing 5. but actually in my original file it is showing 10

Re: Expression error?

Tue, 11 Mar 2014 09:28:18 GMT

Count(DISTINCT {$<Date={">=$(=min(Date))<=$(=max(Date))"}Source={'ITDepartment'},Order={ITDepartment'},Result={'Work Completed'},Flag={1}>}EmployeeID)

Re: Expression error?

Tue, 11 Mar 2014 09:31:26 GMT

Thanks and it's not working. Any help

Re: Expression error?

Tue, 11 Mar 2014 09:33:36 GMT

Thanks. same it's not working

Re: Expression error?

ashwanin — Tue, 11 Mar 2014 09:37:23 GMT

Expression seems to be perfect. Might be you have to check the database for this time frame for the respective entries.

Re: Expression error?

er_mohit — Tue, 11 Mar 2014 09:41:02 GMT

See the attached file

your data is ok there's only 5 id for Result={'Work Completed'}

Re: Expression error?

Tue, 11 Mar 2014 09:45:11 GMT

try to write in same date format like this

SUPPOSE YOUR DATE FORMAT IN 'DD-MM-YYYY'

THEN TRY THIS

Count(DISTINCT {$<Date={">=$(=Date(min(Date),'DD-MM-YYYY'))<=$(=Date(max(Date),'DD-MM-YYYY'))"}Source={'ITDepartment'},Order={ITDepartment'},Result={'Work Completed'},Flag={1}>}EmployeeID)

Re: Expression error?

MK_QSL — Tue, 11 Mar 2014 09:47:21 GMT

There is definitely a problem in your DATE dimension.

If you create a list box for Date you can see multiple occurrence of the same Date...

Looking wrong...

Re: Expression error?

Tue, 11 Mar 2014 09:47:41 GMT

Thanks. this is sample data so it is showing 5 but in the original file it is showing 10. the database table data is also ok. it seems really strange. any ideas?

Re: Expression error?

senpradip007 — Tue, 11 Mar 2014 09:47:50 GMT

Can you attach original application?

Re: Expression error?

Tue, 11 Mar 2014 09:50:21 GMT

Yes you are right. but I joined this date with clendar date. so in the original file it is taking only single occurance. Any help please

Re: Expression error?

MK_QSL — Tue, 11 Mar 2014 09:51:21 GMT

We can help only if you give us either proper sample data file or your sample apps as the expression is looking right.

Re: Expression error?

sudeepkm — Tue, 11 Mar 2014 09:51:28 GMT

I see 5 records from your QVW but you said you found 10 records for the selections you described. Am I missing something?

Re: Expression error?

Tue, 11 Mar 2014 10:23:56 GMT

Hi,

can you upload your qvw?