Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sudeep_d
Not applicable

count issue

contract   yearmnth     type

123             201206      a

123              201205     b

123               201204    c

543               201209    d

543               201209    e

543              201208     f

In the above e.g, i want the count as 1 for contract 123 since the max month is appearing once i.e 201206

   but in 543,count should be 2 since maxmonth for this contract appears twice.. i.e 201209

Please suggest me the required expression.

Thanks

1 Solution

Accepted Solutions
manideep78
Not applicable

Re: count issue

Hi

try this

=Count({<yearmnth={"$(=max(yearmnth))"}>}contract)

See attached qvw

Hope this helps.

6 Replies
sujeetsingh
Not applicable

Re: count issue

Just use countdistinct() and put values and conditions you can easily get a Status as 1 2 or what you want.

Not applicable

Re: count issue

Hi Sudeep,

Please find the attached doc, hope it helps you.

Not applicable

Re: count issue

hi,

you can acchieve this with the following script

table1:

LOAD * INLINE [
    contact, yearmnth, type
    123, 201206, a
    123, 201205, b
    123, 201204, c
    543, 201209, d
    543, 201209, e
    543, 201208, f
]
;

table2:
NoConcatenate

LOAD
contact,
max(yearmnth) AS max_month
RESIDENT table1
GROUP BY contact;
LEFT JOIN

LOAD
contact,
yearmnth AS max_month,
Count(yearmnth) AS count
RESIDENT table1
GROUP BY contact, yearmnth;

DROP TABLE table1;

Regards vicky

sudeep_d
Not applicable

Re: count issue

Hi yasin, i want it in a text box when i select 123 count should be 1 and when i select 543 count should be 2

hi vicky,i want it as expression not in script level..

thanks

manideep78
Not applicable

Re: count issue

Hi

try this

=Count({<yearmnth={"$(=max(yearmnth))"}>}contract)

See attached qvw

Hope this helps.

sudeep_d
Not applicable

Re: count issue

Hi manideep,it worked

Thanks