Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
=Count({<acc_int= {'0'},year={[>$(=max(year)-5)]},id={"=Count(Distinct {<acc_int= {'0'},year={[>$(=max(year)-5)]}>} year)=5"}>}DISTINCT id)
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)
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
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?
Hi,
Thanks for taking a look
The result should be 7
best,
agner
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.
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.
Try this:
=Count({<acc_int= {'0'},year={[>$(=max(year)-5)]},id={"=Count(Distinct {<acc_int= {'0'},year={[>$(=max(year)-5)]}>} year)=5"}>}DISTINCT id)
Hi tresesco,
yeah that worked ! thanks
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
Could you share your sample app to work on?