NetworkDays/Min values in expression

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[swuehl]

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 ))

or

=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