topic Re: GROUP ISSUE in QlikView

GROUP ISSUE

Sun, 23 Feb 2014 00:25:40 GMT

In the below expression I am Grouping within [Occurence No]. This expression works fine with the Current Year or Max Year but it does not work for max(Year)-1. Any suggestions

=Count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'},[Line of Business]={'HCS','PSL'}>} DISTINCT [Occurence No])

Re: GROUP ISSUE

maxgro — Sun, 23 Feb 2014 00:47:35 GMT

try

=Count({<[Occurence No]={"=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000"},Year={$(=Max(CloseAdmin_Year)-1)},[Line of Business]={'HCS','PSL'}>} DISTINCT [Occurence No])

Re: GROUP ISSUE

Sun, 23 Feb 2014 00:50:37 GMT

NO its not correct cuz Max(Year)-1 is a part of the Group, U have to do the Max(Year)-1 within the Occurrence Group.

Thnx for the prompt response. Any more suggestions I can try

Re: GROUP ISSUE

maxgro — Sun, 23 Feb 2014 14:58:00 GMT

if your expression is correct, year should be

CloseAdmin_Year= {"$(=max(CloseAdmin_Year)-1)"}

(double or single quote)

my try

count({$

<[Occurrence No]={"=sum({$ <CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>} NET_INDEMNITY_PAID)>0"}

,CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>

<[Occurrence No]={"=sum({$ <CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>} NET_INDEMNITY_PAID)<=1000000"}, CloseAdmin_Year={'$(=max(CloseAdmin_Year)-0)'}

,CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>

}

DISTINCT [Occurrence No])

Re: GROUP ISSUE

Sun, 23 Feb 2014 15:02:06 GMT

Its not a Valid Expression ,something is missing in there , what is (*) doing there?

Re: GROUP ISSUE

maxgro — Sun, 23 Feb 2014 15:05:32 GMT

* is the intersection operator

you find the meaning in the qlikview online help or here Set Analysis: syntaxes, examples

Re: GROUP ISSUE

Sun, 23 Feb 2014 15:09:54 GMT

QV is not recognizing this expressiore its all Red Lines ,something is missing in there I Guess

count({$

<[Occurrence No]={"=sum({$ <CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>} NET_INDEMNITY_PAID)>0"}

,CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>

<[Occurrence No]={"=sum({$ <CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>} NET_INDEMNITY_PAID)<=1000000"}, CloseAdmin_Year={'$(=max(CloseAdmin_Year)-0)'}

,CloseAdmin_Year={'$(=max(CloseAdmin_Year)-1)'},[Line of Business]={HCS,PSL}>

}

DISTINCT [Occurrence No])

Re: GROUP ISSUE

Sun, 23 Feb 2014 15:12:38 GMT

Unfortunately, when using set analysis and complex expressions, red lines can be displayed even if formula is correct. You have to check the results in data to ensure the expression is doing what it is expected.

Re: GROUP ISSUE

Sun, 23 Feb 2014 15:20:59 GMT

No its not working

Re: GROUP ISSUE

SunilChauhan — Sun, 23 Feb 2014 15:27:32 GMT

Hi,

why not create a varible of previous year

vPreviousYear=Max(CloseAdmin_Year)-1

and use blow

=Count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=$(vPreviousYear))'},[Line of Business]={'HCS','PSL'}>} DISTINCT [Occurence No])

if now working could please attached sample app might be we canhelp.

Thanks