topic Re: need help by set analysis in QlikView

need help by set analysis

maternmi — Tue, 17 Dec 2013 09:15:56 GMT

Hello,

I need help by set analyses.

I would like to display the pickup reliability for each selected month in comparision to the total amount of order in this month, unfortunately I don't know how. What I get is the pickup reliability of each month in comparison to the total amount of orders of all selceted months.

The field Pickup_Reliability_Type contains 3 value types, a) not relevant, b) reliable, c) unreliable

The ID is "%VBELN.%UEPOS", its combination of order number and order position

The month filed C_MonthYear is combination of month and year

My dimensions are C_MonthYear and Pickup_Reliability_Type

I used following expression in the bar chart:

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) / count({$<Pickup_Reliability_Type = -{'not relevant'}>} distinct total %VBELN%UEPOS)

I hope you can help me.

Thanks!

Michael

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 09:21:21 GMT

Try like:

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) / count({$<C_MonthYear=,Pickup_Reliability_Type = -{'not relevant'}>} distinct total %VBELN%UEPOS)

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 09:32:09 GMT

Hi,

unfortunately the result is the same. Any further idea?

Michael

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 09:36:04 GMT

this?

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) / count({1<Pickup_Reliability_Type = -{'not relevant'}>} distinct %VBELN%UEPOS)

If not, please post your sample app.

Re: need help by set analysis

jagan — Tue, 17 Dec 2013 09:37:34 GMT

Hi Michael,

If you have any other date fields like year, month, week, quarter then exclude those selections in expression like this

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) / count({$<C_MonthYear=, Month=, Year=, Week=, Quarter=, Pickup_Reliability_Type = -{'not relevant'}>} distinct total %VBELN%UEPOS)

or simply use this

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) / count({1<Pickup_Reliability_Type = -{'not relevant'}>} distinct total %VBELN%UEPOS)

Regards,

Jagan.

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 09:53:45 GMT

Hi Tresesco, hi Jagan,

no, that is not what I want to see. I've selceted three months 09.2013, 10.2013. 11.2013

09.2013 has a total amount of orders 133, thereof are 34 reliable, that means a reliability of 25,6% but I get 8,83 % because my expression consider the total amount order of the three month which is 385.

But the result must be 25,6%

Does it help you?

Michael

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 10:01:21 GMT

Could you please post your sample app (with sample data) with desired output explained ?

Re: Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 11:15:38 GMT

Please find attached an example

Michael

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 11:39:16 GMT

Michael,

I guess your sample qvw doesn''t have enough data to test. There is no member called 'not relevant' in Pickup_Reliability_Type field. Or, am i missing something?

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 11:59:47 GMT

yes, that's right. But I don't need it therefore I use -{'not relevant'}. I just want to get the values mentioned in qvw.

Thanks!

Michael

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 12:03:20 GMT

For 9.2013 I find 31% logical. Can you please explain the logic of expecting 40% (w.r.t. your sample app)?

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 12:13:59 GMT

If you select only 09.2013 you will get a pickup reliability of 40% (4 of 10 orders) --> is the correct value

If you selct two or more months then the pickup reliability changes to 20% (4 of 20 orders ) or to 13% (4 of 30 orders) because the 4 relialbe orders from Semtember will be divided through the total order amount of all selected months.

How can I show in one chart the reliability of each month?

Sep: 04 of 10 orders = 40%

Okt; 06 of 10 orders =60%

Nov: 03 of 10 ordes =30%

Re: need help by set analysis

tresB — Tue, 17 Dec 2013 12:25:07 GMT

Michael Matern wrote:

If you select only 09.2013 you will get a pickup reliability of 40% (4 of 10 orders) --> is the correct value

If I select only 09.2013, I get 31% !

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 12:27:56 GMT

Hi,

have you change anything on the expression?

count({$<Pickup_Reliability_Type = {'reliable'}>} distinct %VBELN%UEPOS) /count({$<Pickup_Reliability_Type = -{'not relevant'}>} distinct total %VBELN%UEPOS)

Best regards,

Michael

Re: need help by set analysis

stabben23 — Tue, 17 Dec 2013 12:37:29 GMT

Hi put in <C_MonthYear> after distinct total

//Staffan

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 12:48:05 GMT

Hi Staffan,

very simple! Thanks! Have you any document which explain the set analysis?

Best regards,

Michael

Re: need help by set analysis

stabben23 — Tue, 17 Dec 2013 12:52:30 GMT

Hi Michael,

Not what I know, but this means that you calculate only on this dimension in the total. You can use sevral like <Dim1,Dim2,Dim.....>.

//Staffan

Re: need help by set analysis

maternmi — Tue, 17 Dec 2013 13:17:26 GMT

Hi Staffan,

Thanks!

Michael