Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

girishrainbow
New Contributor II

sum of something with if conditon

i want sum of values

i have

compaNY NAME
AMOUNT
ABC10
BTY20
ATE10
ZYS50

I WANT THE SUM ON ABC BTY AND ATE WHICH IS 40 IN A BAR

THE FOLLOWING CODE:

Sum({$<COMPANY_NAME = {"A*","B*"}>}AMOUNT)

in bar chart but i am getting all the bar of company name starting from a and b

thanks in advance

Tags (2)
1 Solution

Accepted Solutions
ChennaiahNallani
Contributor III

Re: sum of something with if conditon

Dimension : =if(WildMatch(compaNYNAME,'A*','B*'),'AB','C to Z')

measure : Sum(AMOUNT)

Capture11.PNG

10 Replies
md_qlikview
Contributor II

Re: sum of something with if conditon

Hi,

You can resolve this in multiple ways... one of them could be  as below

You can create a new field say New Company Name in back end, and merge all three values in one value

eg. If(wildmatch(CompanyName, 'ABC', 'BTY', 'ATE'), NewName, CompanyName) as NewCompanyName.

then use NewCompanyName field in the chart as dimension.

Re: sum of something with if conditon

I am not sure what is the issue when you use this?

Sum({$<COMPANY_NAME = {"A*","B*"}>}AMOUNT)

Can you elaborate on the problem?

rscampbell
New Contributor III

Re: sum of something with if conditon

When I copied your table directly with the formula, I got the same results.

However, I realized that I had mistyped the dimension (e.g. COMPANY NAME vs. COMPANY_NAME)

It is case sensitive - have you checked that?

Way I checked that was to Sum({<COMPANY_NAME = {"ABC"}>} AMOUNT) and when it did not yield 10, I went back and checked the dimension.

Arjunarao
Honored Contributor II

Re: sum of something with if conditon

Hi,

Try this way.

Data:

LOAD *, if(WildMatch(COMPANY_NAME,'A*','B*'),1,0) as Flag;

LOAD * INLINE [

COMPANY_NAME,AMOUNT

ABC,10

BTY,20

ATE,10

ZYS,50

];

Capture.PNG

girishrainbow
New Contributor II

Re: sum of something with if conditon

actually i want only 2 bars in chart where there are sum of all values in a 1 bar starting with letter  'A' and 'B'

and the 2 bar with all the remaining like 'C', D........till Z

in my case it is still showing all the bars with there respective company name

Sorry for replying late as i was not so good.

Thanks

girishrainbow
New Contributor II

Re: sum of something with if conditon

Sorry maybe my explanation was not so good i want only 2 bar where 1 bar have sum of values of company name starting with a nd b and the 2 bar has all the other company name

ChennaiahNallani
Contributor III

Re: sum of something with if conditon

Dimension : =if(WildMatch(compaNYNAME,'A*','B*'),'AB','C to Z')

measure : Sum(AMOUNT)

Capture11.PNG

Arjunarao
Honored Contributor II

Re: sum of something with if conditon

Hi girishrainbow

Try like this with Bar chart.

Script:

Data:

LOAD *, if(WildMatch(COMPANY_NAME,'A*','B*'),1,0) as Flag;

LOAD * INLINE [

COMPANY_NAME,AMOUNT

ABC,10

BTY,20

ATE,10

ZYS,50

];

1. Do not select dimension.

1.png

2. Company AB expression:

2.png

3. Company Others expression:

3.png

4.Output

4.PNG

girishrainbow
New Contributor II

Re: sum of something with if conditon

Thanks this worked for me i did not what to change much in script as i little new changing in  script looks a difficult task for me although i know i will have to learn it if i want to be in this

Community Browser