Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
try this
=Count({<yearmnth={"$(=max(yearmnth))"}>}contract)
See attached qvw
Hope this helps.
Just use countdistinct() and put values and conditions you can easily get a Status as 1 2 or what you want.
Hi Sudeep,
Please find the attached doc, hope it helps you.
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
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
Hi
try this
=Count({<yearmnth={"$(=max(yearmnth))"}>}contract)
See attached qvw
Hope this helps.
Hi manideep,it worked
Thanks