Calculations Diagram or Skript

[#1]

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

[#2]

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))

[PrashantSangle]

Can you explain with some sample data???

Regards

[#4]

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.

[MarcoWedel]

maybe also possible:

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

hope this helps

regards

Marco

[#6]

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 

[PrashantSangle]

Hi,

try aggr with distinct

try like

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

Regards

[#8]

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)

[MarcoWedel]

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

thanks

regards

Marco