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

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
Author

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
Champion III
Champion III

can you post a sample app? with expected output

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

sunny_talwar

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
Author

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!!!

sunny_talwar

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
Author

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

sunny_talwar

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

Not applicable
Author

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)

sunny_talwar

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