How to Count if min(Date) is in the current select... - Qlik Community

Qiyanlu66 · ‎2020-11-23

Hi everyone,

I have a question would like to ask. Here is my data model

I would like to creating a expression to count if the selected year is the minimum year for that Item key

For example, if I select Fiscal Year 2014, the count would be 2, because 2014 is the minimum year of 222222 and 333333.

If I select 2017, the count would be 1 because 111111 has the minimum year of 2017.

I was thinking something like

Count({<[DateYear] = {"$(=aggr(min([DateYear]), distinct [Item Key]))"}>} [Item Key])

But then I realize this will not work, can I get some help on this? Thanks guys.

Kushal_Chawda · ‎2020-11-23

@Qiyanlu66 where you want to use this expression? In chat? If yes, what will be the dimension?

Kushal_Chawda · ‎2020-11-23

@Qiyanlu66 meanwhile try below expression

=count(distinct aggr(if(DateYear = min(total <[Item Key]>DateYear),[Item Key]),[Item Key],DateYear))

MayilVahanan · ‎2020-11-24

Alternate way to achieve the same result like below

=Sum(Aggr(If(DateYear = Min(TOTAL<[Item Key]> DateYear),1), [Item Key]))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

How to Count if min(Date) is in the current selection