topic Re: Calculated dimension by if logic in QlikView

Calculated dimension by if logic

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculated dimension by if logic

prma7799 — Fri, 01 Dec 2017 04:07:21 GMT

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

Re: Calculated dimension by if logic

bhavvibudagam — Fri, 01 Dec 2017 04:18:49 GMT

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

Re: Calculated dimension by if logic

luismadriz — Fri, 01 Dec 2017 04:26:23 GMT

Hi,

I think you may need to try something like this:

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

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

Re: Calculated dimension by if logic

prma7799 — Fri, 01 Dec 2017 04:41:55 GMT

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

Re: Calculated dimension by if logic

luismadriz — Fri, 01 Dec 2017 05:00:17 GMT

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

Re: Calculated dimension by if logic

bhavvibudagam — Fri, 01 Dec 2017 05:03:00 GMT

Thank you so much.

Both the solutions you have provided is working.

Re: Calculated dimension by if logic

sunny_talwar — Fri, 01 Dec 2017 05:46:25 GMT

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