topic Re: Calculations Diagram or Skript in QlikView

Calculations Diagram or Skript

Tue, 04 Jul 2017 02:59:53 GMT

Hi Guys,

i´m relative new to Qlikview but already figured some stuff out.

I have a question regarding calculation in a Skript or Diagram (I probably would prefer the Skript Version).

I got a Field which is called "STATUS" (Fetching it throug a SQL Server with OLE DB) - in that there are the entries: "CAT1", "CAT2, "CAT3", "CAT4", "OC" and "-". The goal is to sum up the numbers of each of them within in the field. At the end I want to be able to make something like that: (Number of strings with (CAT2)) /(Number of strings with(CAT2, CAT3)) for example.

I really would appreciate your help!

Best regards,

Jan

Re: Calculations Diagram or Skript

Tue, 04 Jul 2017 03:46:33 GMT

It nearly worked out like that, but there is another field "Serial" where I got some duplicates..

Because it's a relative calculation it's only a Problem for the Status "OC".

How can I delete these duplicates? (It's not possible with "DISTINCT" because it's another field..)

Thanks!

=(MissingCount({$<STATUS={CAT4}>} STATUS)+

MissingCount({$<STATUS={CAT3}>} STATUS)+

MissingCount({$<STATUS={CAT2}>} STATUS))/

(MissingCount({$<STATUS={OC}>} STATUS )+

MissingCount({$<STATUS={CAT4}>} STATUS)+

MissingCount({$<STATUS={CAT3}>} STATUS)+

MissingCount({$<STATUS={CAT2}>} STATUS))

Re: Calculations Diagram or Skript

PrashantSangle — Tue, 04 Jul 2017 06:52:47 GMT

Can you explain with some sample data???

Regards

Re: Calculations Diagram or Skript

Tue, 04 Jul 2017 07:15:03 GMT

LOAD * Inline [
SERIAL, STATUS, DIV
1234, CAT4, BA
1234, CAT4, BA
1235, CAT2, BB
1236, CAT2, BC
1237, CAT3, BA
1237, CAT3, BA
1238, OC, BB
1238, OC, BB
1238, OC, BB
1239, OC, BA
1239, OC, BA
1240, OC, BC
1241, OC, BC
];

Equation in the Diagramm:

=(MissingCount({$<STATUS={CAT4}>} STATUS)+
MissingCount({$<STATUS={CAT3}>} STATUS)+
MissingCount({$<STATUS={CAT2}>} STATUS))/
(MissingCount({$<STATUS={OC}>} STATUS )+
MissingCount({$<STATUS={CAT4}>} STATUS)+
MissingCount({$<STATUS={CAT3}>} STATUS)+
MissingCount({$<STATUS={CAT2}>} STATUS))

Dimension:

DIV

THANKS! Actually i don't really know how to upload the hole data-sheet.

I have to look that up.

Re: Calculations Diagram or Skript

MarcoWedel — Tue, 04 Jul 2017 22:54:48 GMT

maybe also possible:

=Count({$<STATUS={'CAT2','CAT3','CAT4'}>} STATUS)/Count({$<STATUS={'CAT2','CAT3','CAT4','OC'}>} STATUS)

hope this helps

regards

Marco

Re: Calculations Diagram or Skript

Tue, 04 Jul 2017 23:26:59 GMT

Hi Marco,

thanks for your help! It work's good to simplify the equation, but does not solve the main Problem.

If there is three times the same Serial Number "Count" does Count 3 times, but I want just one count.

In fact the other two entries shall not be countend.

Hope that helps to explain my Problem.

Greetings,

Jan

Re: Calculations Diagram or Skript

PrashantSangle — Wed, 05 Jul 2017 07:15:47 GMT

Hi,

try aggr with distinct

try like

aggr(Count({$<STATUS={'CAT2','CAT3','CAT4'}>} distinct STATUS),serail_number)

Regards

Re: Calculations Diagram or Skript

Wed, 05 Jul 2017 10:58:47 GMT

Hi,

that just will count all my STATUS Entries once I think.. 😕

I'll try tomorrow again, but I think with your hint that should work(?):

aggr(Count({$<STATUS={'CAT2','CAT3','CAT4'}>}STATUS), DISTINCT serial_number)

Re: Calculations Diagram or Skript

MarcoWedel — Wed, 05 Jul 2017 21:47:09 GMT

can you please post an extended example data set as well as your expected result?

thanks

regards

Marco