Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mrthomasshelby
Contributor

Sum of Sales of Vendors Contributing to 80% of Sales

Hello, I have the following data:

Vendor       Sales    Type

A              1300        C

B              2200        N

A              1400        N

B              2100        N

C              2500        C

A              300          N

C              900          C

D              3200        N

B              2000        C

D              1500        C

E              1100        N

F              3500        C

E              1500        C

G              1400        N

H              600          C

H              900          N

G             1200         C


Now, I'd like to Sum up the Sales value of the Vendors of type 'C' contributing to Top 80% of the sales. Can you please help me with the expression for the measure? stalwar1loveisfail‌ I have tried something like this:

Sum({<Type={'C'} Aggr(

    Rangesum(Above(Sum(Sales)/Sum(total Sales),1,RowNo()))<=0.8

    (Vendor,(=Sum(Sales),Desc))

  ))

But it doesn't seem to be right and doesn't work. Thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum of Sales of Vendors Contributing to 80% of Sales

It's not working does exactely mean what? No result, wrong result, computer exploded?

I hope you checked the expression, there is a comma missing before the aggr() dimensions:

Sum({<Type={'C'} >} Aggr(

  If( Rangesum(Above(Sum({<Type={'C'} >} Sales)/Sum({<Type={'C'} >} total Sales),1,RowNo()))<=0.8,

          Sum({<Type={'C'} >}Sales)

    )

,

    (Vendor,(=Sum({<Type={'C'} >}Sales),Desc))

  ))

2017-05-20 15_55_14-.png

4 Replies
MVP
MVP

Re: Sum of Sales of Vendors Contributing to 80% of Sales

Maybe like

Sum({<Type={'C'} >} Aggr(

  If( Rangesum(Above(Sum({<Type={'C'} >} Sales)/Sum({<Type={'C'} >} total Sales),1,RowNo()))<=0.8,

          Sum({<Type={'C'} >}Sales)

    )

    (Vendor,(=Sum({<Type={'C'} >}Sales),Desc))

  ))

mrthomasshelby
Contributor

Re: Sum of Sales of Vendors Contributing to 80% of Sales

Hello. Thanks for the reply. But it's not working. Can you think of any other way maybe? TIA!

MVP
MVP

Re: Sum of Sales of Vendors Contributing to 80% of Sales

It's not working does exactely mean what? No result, wrong result, computer exploded?

I hope you checked the expression, there is a comma missing before the aggr() dimensions:

Sum({<Type={'C'} >} Aggr(

  If( Rangesum(Above(Sum({<Type={'C'} >} Sales)/Sum({<Type={'C'} >} total Sales),1,RowNo()))<=0.8,

          Sum({<Type={'C'} >}Sales)

    )

,

    (Vendor,(=Sum({<Type={'C'} >}Sales),Desc))

  ))

2017-05-20 15_55_14-.png

mrthomasshelby
Contributor

Re: Sum of Sales of Vendors Contributing to 80% of Sales

Thanks a lot! How much difference a single comma makes! This works! You the real MVP!

Community Browser