topic Re: Problem with sum aggr without total amount in QlikView

Problem with sum aggr without total amount

Thu, 03 Jan 2013 11:52:18 GMT

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

Re: Problem with sum aggr without total amount

Gysbert_Wassenaar — Thu, 03 Jan 2013 11:58:11 GMT

Try this:

aggr( IF (

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

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

>65,

VALUE_DLVR),ARTICLE,INVOICE)

Re: Problem with sum aggr without total amount

Thu, 03 Jan 2013 12:34:26 GMT

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

Re: Problem with sum aggr without total amount

Fri, 18 Jan 2013 16:31:41 GMT

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

Gysbert_Wassenaar — Tue, 22 Jan 2013 17:35:56 GMT

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)

)

Re: Problem with sum aggr without total amount

Wed, 23 Jan 2013 16:31:55 GMT

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Re: Problem with sum aggr without total amount

Wed, 23 Jan 2013 16:32:21 GMT

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Re: Problem with sum aggr without total amount

Wed, 23 Jan 2013 16:33:02 GMT

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio

Re: Problem with sum aggr without total amount

Wed, 23 Jan 2013 16:34:13 GMT

Hi Gysbert

your solution is exactly what we need.

Many thanks for your answer.

Regars

Claudio