count distinct id if value equel 0/zero for the last 5 years

[schibler]

Hi all,

I have tried searching the forum but do not seem to be able to find a solution that solves my problem.

I have a data set like the on attached.

What i need is a expression that count the ids, that for the last 5 years have acc_int = 0 (for all the years), to be used text box

Can you help?

thanks

schibler

[schibler]

Yeah sure,

I had to make some changes, but the app is basic the same.

best

agner

[tresesco · Accepted solution]

Try this:

=Count({<acc_int= {'0'},year={[>$(=max({<sector>}year)-5)]},id={"=Count(Distinct {<sector,acc_int= {'0'},year={[>$(=max({<sector>}year)-5)]}>} year)=5"}, sector=>}DISTINCT id)

[schibler]

once again - thanks it works prefect 🙂