Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Problem with sum aggr without total amount

Hi All,

I'm prabably a simple problem but i'm not able to find a good solution.

1) The follow expression doesn't show the total

 

IF (
sum( {$ < DELIVERED ={'>0'} , UNDERSTOCK ={'0'} > }
if(aggr (nodistinct sum(UNDERSTOCK),INVOICE) >0 ,VALUE_DLVR) )
>65,
VALUE_DLVR

)

2) There is another easy way to solve this expression

 

Hire attached the example of qvd files

Many Thanks

Claudio

1 Solution

Accepted Solutions

Re: Problem with sum aggr without total amount

Try this:

aggr( IF (

sum( {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if(aggr (nodistinct sum(UNDERSTOCK),INVOICE) >0 ,VALUE_DLVR) )  

>65,

VALUE_DLVR),ARTICLE,INVOICE)


talk is cheap, supply exceeds demand
8 Replies

Re: Problem with sum aggr without total amount

Try this:

aggr( IF (

sum( {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if(aggr (nodistinct sum(UNDERSTOCK),INVOICE) >0 ,VALUE_DLVR) )  

>65,

VALUE_DLVR),ARTICLE,INVOICE)


talk is cheap, supply exceeds demand
Not applicable

Re: Problem with sum aggr without total amount

Hi

Your Expression right but in expression you took UNDERSTOCK='0'

But you are handling in IF clause sum (UNDERSTOCK)>0,

How it comes I think there you use count  replace of Sum

then it will come.

I think It will helps U

Regards,

Ananth

Not applicable

Re: Problem with sum aggr without total amount

Hi Gysbert

thank you for your appreciate reply.

Now we need to improve our statistic with a more complex case:

As you can find in the attached document we need to considerate all

rows where the total amount > 65

Many Thanks

Re: Problem with sum aggr without total amount

I'm not quite sure what you're asking. Does this do what you want?

if(Dimensionality()=0,

sum(aggr(if(sum(total <INVOICE> [STATUS VALUE])>65,sum([STATUS VALUE]),0),INVOICE)),

if(sum(total <INVOICE> [STATUS VALUE])>65,sum([STATUS VALUE]),0))

Or maybe this:

if(Dimensionality()=0

,sum(aggr(if(sum(total <INVOICE> {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if((aggr (nodistinct sum(UNDERSTOCK),INVOICE)) >0 ,VALUE_DLVR) ) >65,

            sum( {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if((aggr (nodistinct sum(UNDERSTOCK),INVOICE)) >0 ,VALUE_DLVR)),0),INVOICE))

,if(sum(total <INVOICE> {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if((aggr (nodistinct sum(UNDERSTOCK),INVOICE)) >0 ,VALUE_DLVR) ) >65,

            sum( {$ <  DELIVERED  ={'>0'} ,    UNDERSTOCK  ={'0'}  > } 

  if((aggr (nodistinct sum(UNDERSTOCK),INVOICE)) >0 ,VALUE_DLVR)),0)


talk is cheap, supply exceeds demand
Not applicable

Re: Problem with sum aggr without total amount

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable

Re: Problem with sum aggr without total amount

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable

Re: Problem with sum aggr without total amount

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable

Re: Problem with sum aggr without total amount

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Community Browser