topic Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT in QlikView

IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Tue, 07 Feb 2017 08:04:51 GMT

Hi All,

I have a table in QlikView like ID, LAST_PAGE, RO_NO(chronological fill order) AND ERROR_NAME.

First question:

When i get max value from RO_NO in calculated dimension i have for each one rows value=3 and it`s true.

but when i use aggregate max function in expressions i get value only for one row(first not null row). WHY??

Second:

I need show on bar chart count ERROR_NAME only when RO_NO=max(RO_NO).

In this example it should be ERROR3=1.

Case should works for more data, aggregated by ID and LAST_PAGE.

I incolse .qvw file

Can anyone help me??

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

Anil_Babu_Samineni — Wed, 08 Feb 2017 09:34:11 GMT

May be use this Expression

Count({<RO_NO = {'$(=Max(RO_NO))'}>}ERROR_NAME)

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

lironbaram — Wed, 08 Feb 2017 09:36:56 GMT

have a look at the attachment and changes i made to the chart and table

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Wed, 08 Feb 2017 09:48:18 GMT

Thanx Guys

works perfectly:)

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Wed, 08 Feb 2017 11:15:47 GMT

it wasnt work perfectly:(

When i add new ID with other value for RO_NO it stop works:(

expressions show max value for total RO_NO, but should keep max value for agregate group like field in calculated dimension.

Can anyone help one again??

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

Anil_Babu_Samineni — Wed, 08 Feb 2017 11:28:52 GMT

I've added one value

And output is this

Note: Here, Max value is 4 and then we are getting that Max value of ERROR_NAME. In that, what was you are looking for

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Wed, 08 Feb 2017 11:45:38 GMT

BUT WITH LOADED DATA I EXCEPT:

ERROR3=2

ERROR5=1

ERROR6=1

(BECAUSE FOR ID:1909 ERROR_NAME FOR MAX RO_NO IS ERROR3

FOR ID: 2020 ERROR NAME FOR MAX RO_NO IS ERROR5

ID: 1000 ERROR_NAME FOR MAX RO_NO IS ERROR6

AND FOR ID: 1010 ERROR_NAME FOR MAX RO_NO IS ERROR3)

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

Anil_Babu_Samineni — Wed, 08 Feb 2017 12:24:38 GMT

May be try this? And sorry, I forget to see the attachment earlier

Aggr(If(RO_NO=Max(TOTAL <ID> RO_NO), Count(ERROR_NAME)), ID, RO_NO)

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Fri, 10 Feb 2017 13:48:09 GMT

its not working...

have you any idea ??

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

Anonymous — Fri, 10 Feb 2017 14:56:30 GMT

Dual(If(RO_NO=Max(RO_NO), ERROR_NAME, ' '),count(ID))

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Sat, 11 Feb 2017 11:50:54 GMT

This expression count all ERROR_NAME, but i need count only this ERROR_NAME which RO_NO = max(RO_NO) group by ID and LAST_PAGE.

so above expression also doesn`t work:(

Have you any suggestions??

i don`t understend why aggr function for RO_NO grouped by ID and LAST_PAGE doesnt work...

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

Anil_Babu_Samineni — Mon, 13 Feb 2017 07:19:11 GMT

I used this expression, And it worked too

Aggr(If(RO_NO=Max(TOTAL <ID> RO_NO), Count(ERROR_NAME)), ID, RO_NO)

And, Finally why do you need to show ERROR3 data points 2. I doubt what was the logic behind to prove. In fact, Does you mean while you are having same Error of the perticular ID of similar Value. Then you don't want to use NoDistinct Value or what

May be try something like below

Aggr(NODISTINCT If(RO_NO=Max( TOTAL <ID> RO_NO), Count(ERROR_NAME)), ID, RO_NO)

Re: IF FIELD_A=MAX(FIELD_A) THEN COUNT

ambasadorknz — Mon, 13 Feb 2017 10:15:34 GMT

i attache file with more data...

look at the straight table, column called: TRUE,

why i need ERROR3=2 points..??.

because for ID= 1909 and LAST_PAGE=PAGE2 MAX(RO_NO) refer to ERROR3 and for ID=1010 LAST_PAGE=PAGE3 MAX(RO_NO) refer to ERROR3 also. So, if i have ERROR_NAME in dimensions and i count it i have sum=2.

for ID=1909 AND PAGE1 MAX(RO_NO)=1 and it refer to ERROR10,

etc.

that`s why i except:

ERROR3=2

ERROR10=1

ERROR5=1

ERROR6=1