Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Associate AGGR functions ?

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 function.PNG.pngaggr(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


6 Replies
Not applicable
Author

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

regards

Darek

Not applicable
Author

Please find a sample app.

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

regards

Benoît

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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