Calculated dimension by if logic

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

How to calculate the below Excel calculation tin qlikview

=IF(OR(COUNTIF(AB:AH,"R")>0,COUNTIF(AB:AH,"A")>3),"R",IF(COUNTIF(AB:AH,"A")>1,"A","G"))

Thanks in advance.

[prma7799]

What is the logic behind this

=IF(OR(COUNTIF(AB:AH,"R")>0,COUNTIF(AB:AH,"A")>3),"R",IF(COUNTIF(AB:AH,"A")>1,"A","G"))

[bhavvibudagam]

Hi,

Its the excel calculation for above source.

I have tried like below in qlik but its not working getting all G's in the output

=if(count(AB+AC+AD+AE+AF+AG+AH)>0 or

count(AB+AC+AD+AE+AF+AG+AH)>3, 'R',

if(count(AB+AC+AD+AE+AF+AG+AH)>1,'A','G'))

[luismadriz · Accepted solution]

Hi,

I think you may need to try something like this:

=if( RANGESUM(if(AB='R',1,0),if(AC='R',1,0)...) > 0

     OR

     RANGESUM(if(AB='A',1,0),if(AC='A',1,0)...) > 3

     ,'R', ...)

I really hope that someone else could come up with something better...

Cheers,

Luis

[prma7799]

Try this

=if(RangeCount(AB,AC,AD,AE,AF,AG,AH)>0 or

RangeCount(AB,AC,AD,AE,AF,AG,AH)>3, 'R',

if(RangeCount(AB,AC,AD,AE,AF,AG,AH)>1,'A','G'))

[luismadriz]

Hmmm,

I just thought of this one:

=IF((SubStringCount(AB&AC&AD&AE&AF&AG&AH, 'R') > 0 OR SubStringCount(AB&AC&AD&AE&AF&AG&AH, 'A') > 3),'R', IF(SubStringCount(AB&AC&AD&AE&AF&AG&AH, 'A') > 1,'A','G'))

I haven't tried it,

Cheers,

Luis

[bhavvibudagam]

Thank you so much.

Both the solutions you have provided is working.

[sunny_talwar]

Another one might be like this

If(RangeSum(Fabs(AB='R'), Fabs(AC='R'), Fabs(AD='R'), Fabs(AE='R'), Fabs(AF='R'), Fabs(AG='R'), Fabs(AH='R')) > 0 or RangeSum(Fabs(AB='A'), Fabs(AC='A'), Fabs(AD='A'), Fabs(AE='A'), Fabs(AF='A'), Fabs(AG='A'), Fabs(AH='A')) > 3, 'R',

If(RangeSum(Fabs(AB='A'), Fabs(AC='A'), Fabs(AD='A'), Fabs(AE='A'), Fabs(AF='A'), Fabs(AG='A'), Fabs(AH='A')) > 1, 'A', 'G'))