topic Re: Help Neede ! in QlikView

Help Neede !

sujeetsingh — Wed, 12 Oct 2016 04:54:44 GMT

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

Re: Help Neede !

adamdavi3s — Wed, 12 Oct 2016 06:18:12 GMT

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

Re: Help Neede !

miskin_m — Wed, 12 Oct 2016 07:13:32 GMT

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)

Re: Help Neede !

sujeetsingh — Wed, 12 Oct 2016 08:37:01 GMT

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.

Re: Help Neede !

sunny_talwar — Wed, 12 Oct 2016 09:22:03 GMT

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

Re: Help Neede !

sujeetsingh — Wed, 12 Oct 2016 09:27:51 GMT

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.

Re: Help Neede !

adamdavi3s — Wed, 12 Oct 2016 09:28:17 GMT

I am assuming if 2014 say was selected:

1-2

2-2

3-3

4-1

0- everything else so 4

Re: Help Neede !

sunny_talwar — Wed, 12 Oct 2016 09:31:07 GMT

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

Re: Help Neede !

adamdavi3s — Wed, 12 Oct 2016 09:33:20 GMT

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

Re: Help Neede !

adamdavi3s — Wed, 12 Oct 2016 09:40:40 GMT

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

Re: Help Neede !

sunny_talwar — Wed, 12 Oct 2016 09:46:25 GMT

I am still not sure what needs fixing. Are you only trying fix YearGap = 0 or is the whole thing not working?

Re: Help Neede !

sujeetsingh — Wed, 12 Oct 2016 10:03:35 GMT

Now Select Year 2011. Count for 0 year gap is 3. ID 1,2,3 is counted. But it should be 0 count since these ID 1,2,3 is included in Year gap 1.

Re: Help Neede !

sunny_talwar — Wed, 12 Oct 2016 10:16:51 GMT

I am starting to understand this better now. Can you give an example where yeargap will have a value to show?

Re: Help Neede !

adamdavi3s — Wed, 12 Oct 2016 10:27:02 GMT

I am glad one of us is

Re: Help Neede !

sujeetsingh — Wed, 12 Oct 2016 14:14:32 GMT

Here comes the solution Use of Aggr function and excluding already included ID.