Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

8 Replies
Not applicable

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

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Calculations Diagram or Skript

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

maybe also possible:

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

hope this helps

regards

Marco

Not applicable

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

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

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