topic Re: Percentage Issue in If Else Condition in QlikView

Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 09:37:50 GMT

Hi Guys,

I am trying to calculate the percentage value if specific criteria meets. I don't know why If Else condition is not working . . The charts will be my proposed output. Please review the attached screenshots. Please help.

Kind Regards

Waqas Shah

Re: Percentage Issue in If Else Condition

joseph_eftamand — Wed, 19 Sep 2018 10:16:52 GMT

Have you tried using set analysis?

Sum({<Specialty = {'SpecialtyName'} >} SNA) / Sum({<Specialty = {'SpecialtyName'} >} Total SNA)

Let me know if that works for you.

Thanks,

Joseph

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 10:28:53 GMT

Thanks for your reply. No I have not use set Analysis before. I will try this but do you have any idea what's wrong with my script?

Surely I will try set analysis

regards

Waqas Shah

Re: Percentage Issue in If Else Condition

joseph_eftamand — Wed, 19 Sep 2018 10:33:05 GMT

The SNA field is underlined indicating QV may not recognise the field. Also what tis the field TotalSNA?

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 10:36:46 GMT

I just change the names .. Please have a look now with your set analysis.. but no luck,,, please have a look now

Re: Percentage Issue in If Else Condition

joseph_eftamand — Wed, 19 Sep 2018 10:42:53 GMT

can you send a small sample of data to test?

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 10:50:15 GMT

I have attached sample data ..

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 11:07:11 GMT

Based on your sample data out of Excel:

On the left is the data load, on the right the percentage.

I don't understand the relation between the DNA field and DNA total though.

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 11:23:24 GMT

Thanks for your reply. Please review the updated dataset.

DNA means Did Not Attend

DNATotal = DNA + Attends

Like We have DNA Speciality called Surgery e.g. . we need to know in the financial year of 2017/2018 what were the percentage of patents who did not attend their clinicians. I hope it makes sense.

Kind Regards

Waqas Shah

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 11:36:28 GMT

Makes sense.

Please take a look at the image below if this is the desired result:

Loaded data is on the left.

Table on the right gives you the percentage of DNA for that specialty in the financial year.

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 11:46:53 GMT

Yes That's is correct. Please note at a time on screen we show only one Speciality like surgery 25% in 2017/2018 and surgery 2018/2019 is 33.33 %. please review screenshot for desired output (just sample).. how it can be achieved in qlikview?

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 11:58:57 GMT

Hi Waqas

It depends how you want to show one specialty.

1) You can do it through selection, thus using the measure with financial year as a dimension and the specialty selected in a listbox.

2) You can make a separate graph for a specialty and use set analysis to choose which specialty.

Do note that the second method is static.

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 12:03:32 GMT

Many Thanks are you using set Analysis?

Can you please upload the qvd file?

regards

Waqas Shah

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 12:10:28 GMT

In the above screenshot, I am simple making use of a bar graph and a selection field of Specialty.

No use of Set Analysis.

I'm afraid I can't share the example file, as I'm using Qlik Sense (.qvf file vs .qvw file)

The solution works on QlikView and Qlik Sense though.

Steps to undertake:

Create a selection field of Specialty.
Make a bar graph
1. Dimension is Financial Year
2. Measure is: Sum(DNA)/Sum(total<DNASpecialty,[Financial Year]>DNATotal)
Finished.

You can now use the selection field to switch between the specialties.

If your issue is resolved, please mark the correct answer and/or any helpful answers. Thanks.

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 12:16:03 GMT

it works. Many Thanks for your help. Really Appreciated.

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 12:22:46 GMT

You're welcome!

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 13:49:05 GMT

Apologise again but the following expression you write is not set analysis ?

Sum(DNA)/Sum(total<DNASpecialty,[Financial Year]>DNATotal)

what you called it then? because it has different brackets like <> ??

Re: Percentage Issue in If Else Condition

timpoismans — Wed, 19 Sep 2018 14:19:31 GMT

No worries Waqas.

total<DNASpecialty,[Financial Year]>

is not Set Analysis, it's the total function used in a calculation.

Set Analysis is marked such as the following (in bold):

Sum({<Specialty={'Surgery'}>}DNA)

This measure would give me the sum of DNA where the Specialty = Surgery.

Re: Percentage Issue in If Else Condition

waqasshah — Wed, 19 Sep 2018 15:01:48 GMT

ok Got It many Thanks