GROUP ISSUE

[#1]

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])

[maxgro]

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])

[#3]

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

[maxgro]

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])

[#5]

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

[maxgro]

* is the intersection operator

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

[#7]

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])

[#8]

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.

[#9]

No its not working

[SunilChauhan]

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