topic Question on Count based on 2 fields in QlikView

Question on Count based on 2 fields

don_qlikview — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am having a situation where i am unable to figure out the expression for Count based on 2 fields. I am trying to create a pie chart with Status as dimension and in the expression, i have to calculate the count of Status on a distinct [CD Key] and the max date. I tried to use Aggr(), several combinations of Count and distinct. Can any one help out?

Thanks in advance.

Re: Question on Count based on 2 fields

Kushal_Chawda — Thu, 13 Jul 2017 13:58:00 GMT

try this

=count({<Status={'A'}, Date={"$(=date(max(Date))"}>} distinct [CD Key])

Re: Question on Count based on 2 fields

sunny_talwar — Thu, 13 Jul 2017 14:01:28 GMT

But this might not work if the max date is different for each Status... if that's true than may be this

Count(DISTINCT Aggr(If(Date = Max(TOTAL <Status> Date), [CD Key]), Status, Date))