Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sudeep_d
Partner - Creator
Partner - Creator

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
Partner - Specialist
Partner - Specialist

Hi

try this

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

See attached qvw

Hope this helps.

View solution in original post

6 Replies
sujeetsingh
Master III
Master III

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

Not applicable

Hi Sudeep,

Please find the attached doc, hope it helps you.

Not applicable

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
Partner - Creator
Partner - Creator
Author

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
Partner - Specialist
Partner - Specialist

Hi

try this

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

See attached qvw

Hope this helps.

sudeep_d
Partner - Creator
Partner - Creator
Author

Hi manideep,it worked

Thanks