count issue

[sudeep_d]

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

[sujeetsingh]

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

[#3]

Hi Sudeep,

Please find the attached doc, hope it helps you.

[#4]

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]

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 · Accepted solution]

Hi

try this

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

See attached qvw

Hope this helps.

[sudeep_d]

Hi manideep,it worked

Thanks