Associate AGGR functions ?

[#1]

Dear all,

I'm working with sales documents/ sales document lines and the delivery document/ delivery document lines

A sales sales document could have several schedule lines .

I need to count the number of Schedulines with the following rules

ActualGoodsMovementDate <PlannedGoodsIssueDate

& one delivery document per Scheduline Lines !! (we do not want to count sales lines deliver in several times)

aggr(sum(

The following formula is working, but how I can add the case count(DelvDoc)=1 ?

aggr( 

      sum(

             if( "ActualGoodsMovementDate" <= "PlannedGoodsIssueDate"  ,1, 0)

             )

   ,SalesDocLine)

thanks in advance for your help.

Regards

[#2]

May you share sample app? It is always simplier to create expression having data model.

regards

Darek

[#3]

Please find a sample app.

The trick is part this also must work with a "PivotTable" object 😉

regards

Benoît

[#4]

You can try either of these:

aggr(

      sum(

                   if( "ActualGoodsMovementDate" <= "PlannedGoodsIssueDate"

                   and   count(DelvDoc)=1

                   ,1, 0)

             )

   ,SalesDocLine)

or

If(Count(DelvDoc)  = 1,

aggr(

      sum(

             if( "ActualGoodsMovementDate" <= "PlannedGoodsIssueDate"  ,1, 0)

             )

   ,SalesDocLine)

)

Not sure if either of these solve the issue, hope it helps

[#5]

Hello,

unfortunately, as an analyst I have to ask you a few questions .
1 I see that each of SaleDoc = 73526 and 73527 have two different DeliveryDoc, but in one case two different SaleLine, the second is the same for both DeliveryDoc SaleLIne. Are the two cases are
correct?
2 May one DeliveryDoc cover several SaleDoc?
3 At what level are determined date ActualGoodsMovementDate and PlannedGoodsIssueDate?
4 Whether or not you admit the possibility of changes in the data model? (From my experience I know that sometimes change model can greatly simplify the expression, thus facilitate the developement and improve performance .... )

regards
Darek

[#6]

My first recommendation will be to use additional column calculated during reload.

if(ActualGoodsMovementDate < PlannedGoodsIssueDate,1,0) as date_flag

It will be easier to find interesting rows, using set analysis:

{<date_flag={1}>}

Please try to answer my questions from previous post!

regards

Darek

[#7]

i'am still not sure if i can properly understand your needs, so i'am not sure if i have good result :), anyway look my example.

Let me know if it helps.

regards

Darek