topic NetworkDays/Min values in expression in QlikView

NetworkDays/Min values in expression

Tue, 23 Oct 2012 14:18:42 GMT

I have 2 fields - [Created on] and [Picking date]

For instance:

[Doc Number] [Created on] [Picking date]

70215486 2012-05-31 2012-06-01

2012-06-01

Im encountering a problem when trying to find the NetworkDays between [Created on] and [Picking date]

Here im given two value for NetworkDays (1,2) in my dropdown list

However, in order to make my pivot table make sense, I can only have 1 value for this field

For an expression I have the following:

=sum(aggr(if(networkdays_diff >1, count(distinct(DOC_NUMBER)), '0'),DOC_NUMBER ))

Is there an easy way I can have my NetworkDays_difference value look at the minimum([Created on]) value and generate only 1 value?

The min/max functions will not work in the script...

Many Thanks

Re: NetworkDays/Min values in expression

Gysbert_Wassenaar — Tue, 23 Oct 2012 14:55:09 GMT

The min and max functions do work in the script, but you'll have to group by on all the columns you don't aggregate.

It's not quite clear to me what your problem is and what you're trying to achieve. Could you explain more about the data you're working on? If you could post the qvw you're working on that would be most helpful.

Re: NetworkDays/Min values in expression

Tue, 23 Oct 2012 15:56:29 GMT

Thanks for the reply.

This is my scrpit

Sales:

NOCONCATENATE

LOAD [Delivery Docket],
          [Created on],
          [Doc Number],
          [Picking date],
          networkdays(min([Created on]), min([Picking date])) as networkdays_diff_min,
          networkdays([Created on], [Picking date]) as networkdays_diff2

FROM

D:\QlikViewReports\Sales_document.QVD (qvd)
Group By [Delivery Docket], [Created on], DOC_NUMBER, [Picking date];

After I load this script I have these values associated with the [Doc Number] 70016704

[Doc Number] [Created on] [Picking date] networkdays_diff_min

70016704 2012-06-26 2012-06-27 2

2012-06-27 2012-07-03 5

So, when I use the following expression the result which is returned is 0, when it should be 1 - (count(distinct(DOC_NUMBER)))

=sum(aggr(if(networkdays_diff_min >1, count(distinct(DOC_NUMBER)), '0'),DOC_NUMBER ))

Hopefully this makes more sense...

Thanks

Re: NetworkDays/Min values in expression

swuehl — Tue, 23 Oct 2012 18:02:06 GMT

If I understand your issue correctly, networkdays_diff_min is ambiguous for a given DOC_NUMBER. That's why you don't get the distinct count of 1, but zero.

I am not quite sure what the expected outcome is when looking at more than one Doc Number.

Maybe try something like

=sum(aggr(if( min(networkdays_diff_min) >1, 1, 0),DOC_NUMBER ))

=sum(aggr(if(networkdays_diff_min >1, 1, 0 ),DOC_NUMBER, [Created on] ))

[edit]: the last expression is probably not what you want, it doesn't return a distinct count of DOC_NUMBER