topic Re: Counting an expression in QlikView

Counting an expression

Thu, 08 Jan 2015 11:46:20 GMT

Hi all,

Good day.

Is there any way that I can count the number of times that the certain sum of an expression is equal and/or higher than the sum of another expression?

Here's what I want to do:

Having date and product as dimensions, i've got these expressions:

Sum(Qty ordered)

Sum (Qty shipped)

Important: Both quantities come from different tables.

Whenever Qty shipped is equal or higher than Qty Ordered, I have a calculated QV expression with the if function called the Shipment Status that sets its value to '1'. Here's an example.

Days 01.12 02.12 03.12 04.12 05.12

Product A Sum(ordered) 2 2 2 2 2

Sum(shipped) 1 1 2 2 3

Shipment Status 0 0 1 1 1

What I need is to count the number of times, by product, that the shipment qty was equal or greater than the ordered.

Does anyone have any ideas on this?

Thanks!

Re: Counting an expression

jonathandienst — Thu, 08 Jan 2015 11:52:18 GMT

If the Product and the Date are dimensions on a chart or table, then use this for the status column/row

=If(Sum(Qty shipped) > Sum(Qty ordered), 1, 0)

This assumes that they are correctly associated with the same date.

HTH

Jonathan

Re: Counting an expression

Thu, 08 Jan 2015 11:54:19 GMT

Hi,

set an indicator in your load script and assign whenever your criteria are met 1 to FieldCount else 0

in the graph use sum(FieldCount) in order to count...

hth

Re: Counting an expression

agomes1971 — Thu, 08 Jan 2015 12:01:24 GMT

Hi,

Yes this should do the trick...

Regards

André Gomes

Re: Counting an expression

Thu, 08 Jan 2015 12:08:42 GMT

Hi Felix,

Thanks your your reply.

How would that exactly be done?

Quantity Ordered and Quantity Shipped come from different tables that relate to each other using Product and Date as keys.

Re: Counting an expression

Thu, 08 Jan 2015 12:10:05 GMT

Hi Jonathan,

Thanks your for reply.

I'm already using that expression.

What I want to do is to count the number of times I get a 1.

Re: Counting an expression

Thu, 08 Jan 2015 13:30:42 GMT

Hi,

you can read and merge the 2 tables using the join or concatenate statements

and add the countfield depending on the criteria you have.

cheers