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

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
Gysbert_Wassenaar

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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
Author

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
Author

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

Gysbert_Wassenaar

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
Author

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable
Author

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable
Author

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Not applicable
Author

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio