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

hmm i think i managed to solve it  😃

=count(Aggr(IF(COUNT({<year={'>$(vLast5Year)'}, acc_int= {'0'} >} id) =5, count(DISTINCT id)),id))

vLast5Year = max(year)-5

[tresesco]

Looking at your expression, it seems that there could probably be a better/simpler expression for the same. What is expected output from your sample data?

[schibler]

Hi,

Thanks for taking a look

The result should be 7

best,

agner

[tresesco]

Try this?

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

This might give you the expected result (as yours), however, I am not sure about your requirement. If you need to those ids that are there in all last five years, this might not be the correct expression for all scenarios. That is if there are cases when ids might repeat in a year - this expression might not give you the expected output.

[schibler]

hi Tressesco

Yeah, i need it for all the 5 year - it seem to come out blank (result = 0). if it blank for 1 of the 5 year, then it should not be counted in the final result.

[tresesco · Accepted solution]

Try this:

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

[schibler]

Hi tresesco,

yeah that worked ! thanks

[schibler]

Hi @tresesco

I have a followup question. Every ID is connected to a sector and would i like to show, the different sector's results in a barchat  - like showed below. So when a sector is selected, the select sector (bar) is highlights.

Is it possible to extend your expression, so i can be used in a barchat. normally is would just add a "sektor =" in the expression, but that seems not to work/have no effect

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

It is only the expression, the colour-expression is under control 🙂 . a new testdataset is attached with the sectors

Best

[tresesco]

Could you share your sample app to work on?