topic Re: Re: Help in expression as the expression is not working? in QlikView

Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:06:12 GMT

Hi,

I have Straight table. The data is coming from excel sheet.

Dimension: Member

Expressions are

Number: Count(Distinct(ID))

Fail: count({<[Result]={'Fail'}>}Distinct ID)

When I am using the second expression(Fail) the values are showing zeroes eventhough the excel sheet contains data for fail. Please can anayone help me.

Thanks.

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:06:54 GMT

Please find attached.

EDIT: In excel sheet the overall result contains Pass and Fail. But the values in the Result contains '1' and 'Blanks'

Here '1' means fail and 'Blamkns' means pass.

Re: Re: Help in expression as the expression is not working?

srchilukoori — Mon, 24 Mar 2014 15:09:34 GMT

You are using a wrong field. You need to use Overall Result = Fail

Re: Help in expression as the expression is not working?

its_anandrjs — Mon, 24 Mar 2014 15:09:49 GMT

Check your expression like

count(Distinct {< [Result] = {'Fail'} >} ID)

Hope this helps

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:11:37 GMT

Yes you are right. I tried that already. I have a doubt here if I use Overall Result will I get correct values for below?

Noof Reports Reviewed, No of Failures, % of Failures for Member and for Test?

Re: Help in expression as the expression is not working?

its_anandrjs — Mon, 24 Mar 2014 15:12:31 GMT

You have to use

count(DISTINCT {<[Overall Result] = {'Fail'}>} ID)

Hope this helps

Thanks & Regards

Re: Help in expression as the expression is not working?

SunilChauhan — Mon, 24 Mar 2014 15:14:39 GMT

use [Overall Result] inplace

you r using wrong field thats why its showing zero

count({<[Overall Result]={'Fail'}>}Distinct ID)

Re: Help in expression as the expression is not working?

srchilukoori — Mon, 24 Mar 2014 15:16:05 GMT

I don't follow the question about the No.of Reports reviewed...

I don't see any such fields in the app.

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:17:16 GMT

sorry. It is Numebr, Fail and % of Fail.

Will I get the correct values for above?

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:21:57 GMT

Thanks. It's working.

EDIT: I suppose I have mistaken as I thought the data in crosstabel is correct. Please help me as I attached excel and let me know how to sort this?

Re: Help in expression as the expression is not working?

arsal_90 — Mon, 24 Mar 2014 15:23:29 GMT

Hi Amalia could you please share the excel file because the data is coming in the load table is wrong

and expression to correct =count( {<[Overall Result] = {'Fail'}>} DISTINCT ID)

Re: Help in expression as the expression is not working?

srchilukoori — Mon, 24 Mar 2014 15:24:08 GMT

I think there is an error in your cross table load. The Test field has same data as the Result. Take a look at it, that might help you with the logic.

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:36:43 GMT

Please find excel and let me know.

Re: Help in expression as the expression is not working?

srchilukoori — Mon, 24 Mar 2014 15:40:07 GMT

what are fileds 1A to 5E?

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:42:34 GMT

Those are Error Types for each Member whether they Pass or Fail in doing the work. 1 denotes Fail and Blanks denotes Pass.

Re: Help in expression as the expression is not working?

MK_QSL — Mon, 24 Mar 2014 15:49:31 GMT

Like this?

S20097 is coming two times. i.e under adm YY and Under adm ZZ

Re: Help in expression as the expression is not working?

Mon, 24 Mar 2014 15:53:59 GMT

Thanks. But the Test and Result are showing same values for most of the ones. Is it like that when we use CrossTable?

Re: Help in expression as the expression is not working?

MK_QSL — Mon, 24 Mar 2014 16:03:12 GMT

Actually, I didn't understood your requirement properly.

Re: Re: Help in expression as the expression is not working?

srchilukoori — Mon, 24 Mar 2014 16:09:14 GMT

please go through the attached app for the updated cross table. You can transform further to reduce the number of records for 'Pass' records.

Re: Re: Help in expression as the expression is not working?

arsal_90 — Mon, 24 Mar 2014 16:13:42 GMT

Hi Amelia

Please find the attached file.

please see cross table statement