8 Replies Latest reply: Jul 5, 2017 5:47 PM by Marco Wedel

# Calculations Diagram or Skript

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

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

Can you explain with some sample data???

Regards

• ###### Re: Calculations Diagram or Skript

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

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

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

Hi,

try aggr with distinct

try like

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

Regards

• ###### Re: Calculations Diagram or Skript

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

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

thanks

regards

Marco