topic Re: Set analysis - percentage calculation in QlikView

Set analysis - percentage calculation

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a question for you regarding the problem that happens me occasionally while I use set analysis.

Set analysis:

% of late deliveries:

count({$<[delivery status]={"LATE"}>} [DELIVERY NOTE UNIQUE ID])/(Count([DELIVERY NOTE UNIQUE ID]))*100

% of deliveries on time:

count({$<[delivery status]={"ON TIME"}>} [DELIVERY NOTE UNIQUE ID])/(Count([DELIVERY NOTE UNIQUE ID]))*100

Delivery note can have 2 statuses: ON TIME or LATE (there is no third possibility).

This analysis shows correct results in all cases, expect for one. In the case when I select either filter "ON TIME" or "LATE", I get calculated % values that are above 100%, or the sum of these two values is higher than 100% (what is not expected).

Have other people faced situation that % value calculated with set analysis is higher then 100%, and how you resolved it?

Regards

Vera

Re: Set analysis - percentage calculation

MK_QSL — Mon, 16 Jan 2017 13:46:10 GMT

Try

count({$<[delivery status]={"LATE"}>} [DELIVERY NOTE UNIQUE ID])

(Count(Total [DELIVERY NOTE UNIQUE ID]))*100

and

count({$<[delivery status]={"ON TIME"}>} [DELIVERY NOTE UNIQUE ID])

(Count(Total [DELIVERY NOTE UNIQUE ID]))*100

Re: Set analysis - percentage calculation

swuehl — Mon, 16 Jan 2017 13:49:12 GMT

You probably need to ignore the selection in field [delivery status] in the denominator:

for example:

=count({$<[delivery status]={"LATE"}>} [DELIVERY NOTE UNIQUE ID])

(Count({<[delivery status] = >} [DELIVERY NOTE UNIQUE ID]))

*100

Re: Set analysis - percentage calculation

Mon, 16 Jan 2017 13:50:08 GMT

Thank you very much for proposal. Unfortunately, the problem with %>100% still occurs.

Regards
Vera

Re: Set analysis - percentage calculation

MK_QSL — Mon, 16 Jan 2017 13:51:14 GMT

=Num(count({$<[delivery status]={"LATE"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

and

=Num(count({$<[delivery status]={"ON TIME"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

Updated : Before me Stefan has already replied. ..

Re: Set analysis - percentage calculation

Mon, 16 Jan 2017 13:58:24 GMT

Hi, thanks for proposal.

Now during selection of filter ON TIME/LATE doesn't affect set analysis. All other filters work normally. Is there a way to update set analysis formula so it can be sensitive to selection of DELIVERY STATUS FILTER (ON TIME/LATE), while it shows correct results (100% or 0% for selection of one of the filtration criteria)?

Thanks

Vera

Re: Set analysis - percentage calculation

MK_QSL — Mon, 16 Jan 2017 14:11:02 GMT

=Num(count({$<[delivery status]*={"LATE"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

and

=Num(count({$<[delivery status]*={"ON TIME"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

Re: Set analysis - percentage calculation

eniemenm — Mon, 16 Jan 2017 14:28:07 GMT

By using the set excluding operator minus, you propably achieve what you are looking for:

count({<[delivery status] -= {'ON TIME'}>}[DELIVERY NOTE UNIQUE ID]) / count([DELIVERY NOTE UNIQUE ID]) * 100

This way you can exclude results related to delivery status "on time" but if "on time" is selected, it wont give the results related to "late".

Re: Set analysis - percentage calculation

Tue, 17 Jan 2017 06:45:50 GMT

Hi Eerno,

It seems we are close, but not exactly at the right place. After adding set excluding operator minus, when I select "on time" filter, I get 0% for on time and correct number for "late" (and vice versa).

Is there some operator that excludes everything but selected value?

Thank you all on your help!

Regards

Vera

Re: Set analysis - percentage calculation

eniemenm — Tue, 17 Jan 2017 06:54:36 GMT

If you wish it to show always the selected from field [delivery status] divided by the total you could use this formula:

count[DELIVERY NOTE UNIQUE ID]) / count({<[delivery status] >}[DELIVERY NOTE UNIQUE ID]) * 100

If this is not what you are looking for, it might help if you provided some examples on how you would expect it to work 😃

Re: Set analysis - percentage calculation

Tue, 17 Jan 2017 07:02:03 GMT

Hi Manish,

This is good progress, but I still don't get fully correct values. After I added *, when I select "on time" status, I get: 0% for "Late" deliveries (what is correct) and <100% for "on time" deliveries (what is not correct because it should be 100%).

Basically, selection of filter On time/late excludes opposite value from calculation, but it doesn't recalculate value, but it keeps the one before I selected "on time/late" filter.

I think that the problem is denominator because in some way it should also be sensitive to the selection made in the field "delivery status".

Thanks for helping!

Regards

Vera

Re: Set analysis - percentage calculation

MK_QSL — Tue, 17 Jan 2017 07:09:17 GMT

It would be better if you provide few lines of sample data and let us know what exactly you are looking for; Believe me you issue is not very complex. But due to lack of data or sample application, people are not taking interest in replying.

Once you upload a sample data or sample application, you can have answer within an hour which you are trying to get since yesterday.

Re: Set analysis - percentage calculation

MK_QSL — Tue, 17 Jan 2017 07:11:54 GMT

My last try. If this will not work.. I need sample data.

=Num(count({$<[delivery status]*={"LATE"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]*={"ON TIME","LATE"}>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

and

=Num(count({$<[delivery status]*={"ON TIME"}>} [DELIVERY NOTE UNIQUE ID])/(Count({<[delivery status]*={"ON TIME","LATE"}>}[DELIVERY NOTE UNIQUE ID])),'#0.0%')

Re: Set analysis - percentage calculation

Tue, 17 Jan 2017 07:16:35 GMT

This is the answer I was looking for! Thanks for advise.

Good tip for samples. I'll prepare the test sample next time when I have a problem.

Re: Set analysis - percentage calculation

Tue, 17 Jan 2017 07:17:47 GMT

Hi Eerno,

Thanks for your comments. It seems Manish managed to help me! I'll pay attention next time to prepare example to make communication easier.

Regards

Vera