Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Bar chart measure with sum() and aggr()

Hi everybody,

I have a bar chart that shows the expenses vs the date. For each date I have objects that have been made with different quantities (I mean, each year and each day of each month the number of produced items is different). To show this, I compute the price of each object (every object is made up  of different small objects with different quantities and so I have to use sum() and Aggr()) and then I need to multiply the price by the produced quantity (I mean that maybe in one day there have been 1000 produced objcets and 700 are of a type, 300 of a different type).

The problem with the chart is that editing the expression I can only get the sum of everything in the middle of the bar chart, or the prices of the objects multiplied by the total quantity and not each one multiplied by its quantity.

If I use total after the sum expression I get what I want, but when I multiply by the produced quantity I get the product of the measure by the total quantity, not the quantity of each product

How can I solve this?

Thanks a lot for your attention

Best regards

Nicolò Cogno

1 Solution

Accepted Solutions
Not applicable

Re: Bar chart measure with sum() and aggr()

I solved using this expression

Sum(total(Aggr(Sum([QUANTITYOFSMALLEROBJECTS]*[QUANTITYOFBIGGEROBJECTS])*Min(PRICEOFSMALLEROBJECTS), SMALLEROBJECTS,BIGGEROBJECTS,DATE)))*Count([FINALOBJECTS])


Hope this helps

View solution in original post

10 Replies
vinieme12
Esteemed Contributor II

Re: Bar chart measure with sum() and aggr()

can you post a sample app? with expected output

Preparing examples for Upload - Reduction and Data Scrambling

Not applicable

Re: Bar chart measure with sum() and aggr()

I am sorry, but I can't do that

I can't aggregate the number of produced objects by the objects, it always compute the total quantity

Re: Bar chart measure with sum() and aggr()

Would you be able to at least share few rows of data and explain what you are looking to get from the sample?

Not applicable

Re: Bar chart measure with sum() and aggr()

This is the expression I'm using in the chart:

Sum(total(Aggr(Sum([QUANTITYOFSMALLEROBJECTS]*[QUANTITYOFBIGGEROBJECTS])*Min(PRICEOFSMALLEROBJECTS), SMALLEROBJECTS)))*Count([FINALOBJECTS])

When I show the results in a table I see correct results for each row (the toal is incorrect instead), but trying to show them in a chart gives the prices of the single objects multiplied by the total quantity of each day and not by the quantity of the specific object (I try to give an example: everyday I produce apples and pears and everyday the total quantity is different. Yesterday I produced 100 fruits: 70 apples and 30 pears. Apples cost 1€, pears cost 2€. I've got a table that contains a row for each item produced, i.e. 70 rows with the different serial numbers of the apples, each one containing also the same date and the same code for a specific kind of apple. The problem of the chart is that it multiplies the price by the total quantity instead of multiply 1€*70 and 2€*30. The thing that identify each single row is the serial number, it varies always.) 

I hope I explained myself and thanks for your help!!!

Re: Bar chart measure with sum() and aggr()

Forgetting about what you just gave me, I will try to give you an expression based on your example

Sum(Aggr(Sum(Quantity) * Avg(Price), Product))

Here Product is Apple or Pear

Not applicable

Re: Bar chart measure with sum() and aggr()

Thanks for your help Sunny, this is what I'm already doing and it works fine, but now I need to multiply by the number of objects that I produce day by day and that's what is not working

Re: Bar chart measure with sum() and aggr()

What is Sum(Quantity) here? Isn't that telling that there are 70 Apples in a day?

Not applicable

Re: Bar chart measure with sum() and aggr()

No Sunny, sorry, it's the quantity of pieces that each object consists of(I'm trying to evaluate the price of complex objects that are made up of smaller objects, like this Qlik Design Blog : Bill of Materials | Qlik Community)

Re: Bar chart measure with sum() and aggr()

Unfortunately, I have no idea what you have and what you want. I will have to let someone else try help you out.

Best,

Sunny