Discussion board where members can get started with Qlik Sense.
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
Solved! Go to Solution.
I solved using this expression
Sum(total(Aggr(Sum([QUANTITYOFSMALLEROBJECTS]*[QUANTITYOFBIGGEROBJECTS])*Min(PRICEOFSMALLEROBJECTS), SMALLEROBJECTS,BIGGEROBJECTS,DATE)))*Count([FINALOBJECTS])
Hope this helps
can you post a sample app? with expected output
Preparing examples for Upload - Reduction and Data Scrambling
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
Would you be able to at least share few rows of data and explain what you are looking to get from the sample?
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!!!
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
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
What is Sum(Quantity) here? Isn't that telling that there are 70 Apples in a day?
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)
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