Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello every one please go through the attached sample file. It has all details you need.
Here comes the solution Use of Aggr function and excluding already included ID.
Sorry but I can't work out your problem from that file
Hi,
If I have understood your problem correctly, you just need to change the first if expression to
if(yeargap=0, Count({<Year= {">$(=Max(Year))"}>} DISTINCT ID)
It is like I need a Dynamic chart which works for selected Year and groups the count of ID for Year gap 1 ,2 ,3 ,4 . Now I want to Count those ID in Year Gap 0 who did not fall in any of the Year gap 1 ,2 ,3 ,4.
Based on the selection made, would the expected output for 0 yeargap be 0?
no it counts those ID which do not fall in Year Gap 1,2,3,4. Hence ) Year Gap means count of ID with No year gap.
I am assuming if 2014 say was selected:
1-2
2-2
3-3
4-1
0- everything else so 4
How did you get those numbers? I am not sure I understand
It's something like this I think...
=pick(match(yeargap,0,1,2,3,4),
Count({<Year= {">=$(=Year+1) <$(=Year-3) "}>} ID),
Count({<Year= {'$(=Year-1)'}>} ID),
Count({<Year= {'$(=Year-2)'}>} ID),
Count({<Year= {'$(=Year-3)'}>} ID),
Count({<Year= {'$(=Year-4)'}>} ID))
So Year selected = 2014
yeargap 1 is count of ID in year 2013
Yeargap 2 is count of ID in year 2012 etc....
My set analysis brain isn't working this morning, I can't even get the simplest of things working