topic Re: Associate AGGR functions ? in QlikView

Associate AGGR functions ?

Wed, 09 Apr 2014 07:09:50 GMT

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

Re: Associate AGGR functions ?

Wed, 09 Apr 2014 07:18:58 GMT

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

regards

Darek

Re: Re: Associate AGGR functions ?

Wed, 09 Apr 2014 07:39:06 GMT

Please find a sample app.

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

regards

Benoît

Re: Associate AGGR functions ?

Wed, 09 Apr 2014 10:30:49 GMT

You can try either of these:

aggr(

sum(

if( "ActualGoodsMovementDate" <= "PlannedGoodsIssueDate"

and count(DelvDoc)=1

,1, 0)

)

,SalesDocLine)

If(Count(DelvDoc) = 1,

aggr(

sum(

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

)

,SalesDocLine)

)

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

Re: Associate AGGR functions ?

Wed, 09 Apr 2014 11:01:00 GMT

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

Re: Associate AGGR functions ?

Wed, 09 Apr 2014 12:25:08 GMT

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

Re: Re: Associate AGGR functions ?

Wed, 09 Apr 2014 13:58:24 GMT

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