Help Neede !

[sujeetsingh]

Hello every one please go through the attached sample file. It has all details you need.

[adamdavi3s]

Sorry but I can't work out your problem from that file

[miskin_m]

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)

[sujeetsingh]

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.

[sunny_talwar]

Based on the selection made, would the expected output for 0 yeargap be 0?

[sujeetsingh]

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.

[adamdavi3s]

I am assuming if 2014 say was selected:

1-2

2-2

3-3

4-1

0- everything else so 4

[sunny_talwar]

How did you get those numbers? I am not sure I understand

[adamdavi3s]

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....

[adamdavi3s]

My set analysis brain isn't working this morning, I can't even get the simplest of things working