topic Re: Null Calculation: Shipped Not Invoiced in QlikView

Null Calculation: Shipped Not Invoiced

Wed, 04 Nov 2015 16:11:27 GMT

My data:

Ship Req #	Shipped Date	Item Code	Description	[Bill Number]	Shipped Value	Shipped Qty
11373	10/28/2015	209104	Acme Apple	9540	299	597
11371	10/28/2015	386008	Charles Cat	9537	1,121	26,568
11371	10/28/2015	386009	Charles Cat	9537	2,296	54,432
11309	10/28/2015	109115A	Doug's Dog	9538	972	528
11376	11/02/2015	103089	Acme Green Apple	9541	14,564	2,896
11432	11/03/2015	581003	Farmers Fox	-	679	2,380
11450	11/03/2015	103089	Acme Green Apple	9541	14,564	2,896

The "Bill Number" column is in a separate column from the other items which are all in one table.

I am trying to create a "Shipped Not Invoiced" chart. The result should be the one line with Ship Req # 11432. Since an invoice has not been created, the Bill Number does not exist.

The code to create the Shipped Value is

sum( $(_SetShippedActualDate)

IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
)
)

How do I sum all "Ship Req #" that do not have a corresponding "Bill Number"?

Thank you for your help!

Re: Null Calculation: Shipped Not Invoiced

Wed, 04 Nov 2015 16:48:25 GMT

you can use something like if(isnull(billnumber),'Not Invoiced','Invoiced') as a flag or you can put this as an and condition 'and isnull(billnumber)' in your condition

Re: Null Calculation: Shipped Not Invoiced

Qrishna — Wed, 04 Nov 2015 16:56:57 GMT

How do I sum all "Ship Req #" that do not have a corresponding "Bill Number"?

Do you want to sum up all the numbers which are like IDs ?

Any ways if you want sum up a field Ship Req # that do not have a corresponding "Bill Number" then :

if(len([Bill Number])=0, Sum([Ship Req #])) - in both script and UI

should work.

Let me know.