topic Re: Relative percentage calculation issue in QlikView

Relative percentage calculation issue

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

Hi Guys,

I have a problem statement for all. I am from payment industry. Let me explain my problem

Day Failure/success reason Txn_Count

1 No funds 10

1 Cancelled 20

1 Not approved by bank 30

1 Successful 40

Total 100

I have put a condition in the expression(Txn_Count) formula that Status='failed' to get below:

Day Failure/success reason Txn_count

1 No funds 10

1 Cancelled 20

1 Not approved 30

I want count to be a relative percent i.e 10% for no funds, 20% for cancelled and 30% for not approved. This percent should be calculated with denominator 100 not 60

Also, This is for only on day. If there are many days and I select No funds failure reason, it should show relative share for all individual days.

1 No Funds 40%

2 No Funds 20%

3 No Funds 10%

Right now its showing 100% for all days. Can anyone help here

Re: Relative percentage calculation issue

sunny_talwar — Tue, 20 Feb 2018 17:13:46 GMT

May be try this

Count({<Status = {'failed'}>}Txn)/Count(TOTAL Txn)

Re: Relative percentage calculation issue

ankit_tikoo — Wed, 21 Feb 2018 06:52:22 GMT

Did not work buddy...still showing 100% for some and 0% for others.

First need is to eliminate the successful info from the table. For that, I have put the expression formula

sum(if(Status='failed',Txn_count,0)

Now the next problem is to find relative percent of failures out of total txns

i.e Status Reason Txn_Count Day

Failed No funds 10% 1

Failed Cancelled 20% 1

Failed Not approved 30% 1

Success Successful 40% 1

Failed No funds 20% 2

Failed Cancelled 20% 2

Failed Not approved 10% 2

Success Successful 50% 2

I want the below

i.e Status Reason Txn_Count Day

Failed No funds 10% 1

Failed Cancelled 20% 1

Failed Not approved 30% 1

Failed No funds 20% 2

Failed Cancelled 20% 2

Failed Not approved 10% 2

I want the above table in bold. Thanks!!

Re: Relative percentage calculation issue

sunny_talwar — Wed, 21 Feb 2018 17:08:28 GMT

Would you be able to share a sample where this did not work?

Count({<Status = {'failed'}>}Txn)/Count(TOTAL Txn)

Did you remove the relative option?

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 27 Feb 2018 11:02:35 GMT

Hi Sharing a file. The problem here is that individual failure percentages should be calculated out of total txns but here that is not happening and I am only getting failure % of an individual failure reason out of total failures.

It should be Failure/total txns rather than failure/total fails

Attaching a file to show you the issue

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 27 Feb 2018 11:03:14 GMT

Relative is still selected

Re: Relative percentage calculation issue

sunny_talwar — Tue, 27 Feb 2018 17:34:48 GMT

Does any one of these look like what you wanted?

Re: Relative percentage calculation issue

pablolabbe — Tue, 27 Feb 2018 19:26:41 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

Re: Relative percentage calculation issue

ankit_tikoo — Wed, 28 Feb 2018 14:25:53 GMT

No all the percentages are same in the above table

For e.g it should have been like below for day 1

Day Comment TXn_count

1 Risk failure 5%

1 Failed at bank end 10%

1 Risk Failure 15%

I had taken total of 100 txns out of which successful were 70,failed were 30.

So basically, I want fail% of individual comments/failure reasons out of total txns which was 100 in this example.

Tried many things but still not getting it.

Re: Relative percentage calculation issue

sunny_talwar — Wed, 28 Feb 2018 14:31:19 GMT

Try this

Sum({<Status = {'Failed'}>}Txn_count)/Sum(TOTAL <Day> Txn_count)

Re: Relative percentage calculation issue

ankit_tikoo — Wed, 28 Feb 2018 14:43:06 GMT

Not working.... All showing 0 value

Re: Relative percentage calculation issue

sunny_talwar — Wed, 28 Feb 2018 14:44:20 GMT

Did you open the file I attached? I seem to be having no problems at all

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 06 Mar 2018 13:40:47 GMT

No it still is not working for me.

Re: Relative percentage calculation issue

sunny_talwar — Tue, 06 Mar 2018 13:43:08 GMT

Is this a screenshot from the file that I attached?

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 06 Mar 2018 13:47:41 GMT

No I tried to apply the formula shared by you in my file. But in my file this does not seem to be working.

Re: Relative percentage calculation issue

sunny_talwar — Tue, 06 Mar 2018 13:50:07 GMT

I am going to let you figure out your own mistake by just showing an image

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 06 Mar 2018 13:54:08 GMT

Mistake corrected. Thanks!!

The data is still not correct. check the %ages

Re: Relative percentage calculation issue

sunny_talwar — Tue, 06 Mar 2018 13:58:57 GMT

I guess it might have gotten lost in my post... but you cannot have 'Relative' option checked... Try removing that

Re: Relative percentage calculation issue

ankit_tikoo — Tue, 06 Mar 2018 14:14:30 GMT

Its works!!! Thanks!!

Re: Relative percentage calculation issue

sunny_talwar — Tue, 06 Mar 2018 14:19:19 GMT

Superb... I am glad that we can finally agree that it works