topic Re: Expression to get correct sum value in QlikView

Expression to get correct sum value

Thu, 01 Oct 2015 14:14:46 GMT

Hello community,

I'm trying to get the sum of costs for a user based on the ID and the company.

Here's my example script which return the wrong sum as you can see in the screenshot.

So the correct output would be 2 for User A in company X and 4 for user B in company Y

The problem is the ChangeID which creates additional rows for each ID.

LOAD * INLINE [

ID, User, ChangeID, Cost, Comany

1, A, 1, 2, X

1, A, 2, 2, X

1, A, 3, 2, X

2, B, 1, 4, Y

2, B, 2, 4, Y

];

Can you show me how this can be done?

Thank you.

Re: Expression to get correct sum value

pamaxeed — Thu, 01 Oct 2015 14:18:17 GMT

Try:

Sum(Aggr(Sum(distinct Cost), User))

Re: Expression to get correct sum value

rubenmarin — Thu, 01 Oct 2015 14:19:22 GMT

Hi Thorsten, one easy solution is using Avg() instead of Sum(), can this work on your complete model?.

For working also in totals you can use:

Sum(Aggr(Avg(Cost), User))

Re: Expression to get correct sum value

nagaiank — Thu, 01 Oct 2015 14:26:07 GMT

Use User as dimension and

Aggr(FirstSortedValue(Cost,-ChangeID),User) as expression

Re: Expression to get correct sum value

MK_QSL — Thu, 01 Oct 2015 14:43:23 GMT

SUM(DISTINCT Cost)

SUM(Cost)/Count(Comany)

AVG(Aggr(SUM(Cost),Comany,ChangeID))

Re: Expression to get correct sum value

Fri, 02 Oct 2015 05:52:21 GMT

Hey guys,

thank you for your answers I had to make a slight modification to get it to work for me.

I also added a few more lines to the data.

In the screenshot you can see that in the table box the sum of User A is 8 over all companies.

Sum(Aggr(Sum(distinct Cost), ID)) did the trick for me.

LOAD * INLINE [

ID, User, ChangeID, Cost, Comany

1, A, 1, 2, X

1, A, 2, 2, X

1, A, 3, 2, X

2, B, 1, 4, Y

2, B, 2, 4, Y

3, A, 8, 2, Y

3, A, 9, 2, Y

3, A, 10, 2, Y

3, A, 11, 2, Y

4, A, 55, 4, Y

4, A, 56, 4, Y

];

Thank you,

Thorsten