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: 
Anonymous
Not applicable

Sum product

Hi Team,

I am new to QLIK view, trying to develop my own dashboard. I am struck with Sum product formula, let me explain my case.

I have each market wise cost and volume, i want to know the overall Region cost. I have given below the sample data. In excel i will write formula as =sum product(Cost,Volume)/Total Volumes. please try to translate it to expression in QLIkview

RegionCostVolumes
Dubai  1,000.002000
Israel            -0
Northern Africa  2,000.008000
Southern Africa  3,000.00120000
Other Africa  4,000.0020000
Middle East & Africa Total  3,053.33150000
24 Replies
sunny_talwar

If you open this link Re: Sum product... you will see the qvw attached with my response.

When you say in KPI, those only show value when one Line Item Grouping is selected?

1) I think that's how it should work and if you want to look at product you can either add it as a dimension or make a selection to view a single product.

2) I am not sure I understand this

Anonymous
Not applicable
Author

Thanks Sunny, you are awesome. I can take this for my internal review with my management.

My plan is to develop 3 KPI

1. Profit Margin (in%) which is nothing but Automotive PBT / (Net revenue + G.Revenue *10%) of my data set

2. In the flash wanted to give the PBT in all the means ($ Mils, Per unit & %)

3. Wanted to show the same KPI as in point 2 when the new product is launched (Launched year)

tripatirao
Creator II
Creator II

Hi Sunny,

why you used Dimensionality() here.

Please let me know

sunny_talwar

1) For which year would want to show this for?

2) When you say Flash, do you mean it toggles between the three based on some time passed like every 3-4 seconds the value changes?

3) Another set of text boxes for new product? but what is the definition of new product?

Anonymous
Not applicable
Author

Sunny,

My company is manufacturing company, They launch multiple products over period of time which has different product cycle. For e.g take case of Samsung, each were different products and they are launching month over month or year over year according to the regional demand and need.

When a particular product is selected, my intention is to display the Operating Margin / Profit Margin in KPI  management would be interested to know only this on high level, when they want to dig deep they can select from the drop down.

The problem here is, i have listed every selection under dimension of "Line Items", i don't know how to call out Operating Margin in KPI as default. If you can help me to develop this KPI that would be helpful.

Anonymous
Not applicable
Author

I have worked on developing a KPI for my dashboard by using the below mentioned formula

=num(sum(aggr (sum( {<[Line Items]={"Operating Margin"},Year={"Cyc Avg"} >} [In %]*Volumes),Market))/sum(aggr (sum( {<[Line Items]={"Operating Margin"},Year={"Cyc Avg"} >} Volumes),Market)),'#,##0.0;(#,##0.0)')&'%'

However i noticed that it was not correct as i require. Instead of multiplying with Volume, i need to use "Net Revenue" which is one of the element in "line Items Grouping". Please help me to correct that formula. I am suppose to get (38.5%) as the Ops margin for KPI. I have listed the numbers below.

MarketLine ItemsIn $ MilsLine ItemsIn %
DubaiNet Revenue21.16097561Operating Margin (36.05)
IsraelNet Revenue0Operating Margin       -  
Northern AfricaNet Revenue28.4195122Operating Margin  19.61
Southern AfricaNet Revenue95.62878456Operating Margin (63.01)
Other AfricaNet Revenue21.42439024Operating Margin   (8.79)

(Sumproduct of Net Rev $ mils * Ops Margin %)/sum of Net Revenue)

-38.52314379
sunny_talwar

Try this

=Sum(Aggr(Sum({<[Line Item Grouping] = {'Net Revenue'}, Year = {'Cyc Avg'}>}[In $ Mils]) * sum({<[Line Item Grouping] = {'Operating Margin'}, Year = {'Cyc Avg'}>}[In %]), Market))/Sum({<[Line Item Grouping] = {'Net Revenue'}, Year = {'Cyc Avg'}>}[In $ Mils])


Capture.PNG

Anonymous
Not applicable
Author

You are awesome...It absolutely worked. However, i have sent you one another request for identifying the total of all region in %. It would be nice, if you can also respond to that as well!!

sunny_talwar

Are you talking about another thread?

Anonymous
Not applicable
Author

Sunny,

I dropped a personal msg to you, that is what i have referenced above. Any way, below is the msg copied from that msg.

Sunny, I tried replicating the same you did for per unit to %, but it didn't work. It will not work coz the formula for % is something different.

Generally it is sum(in %), for MEA Total it should be sum product of (Operating Margin%:Net Revenue)/Total Net revenue.

I have tried this, but not working out. Your help is needed again.

=sum({<[Line Item Grouping] = {'Operating Margin'}>}sum([In %]*sum({<[Line Item Grouping] = {'Net Revenue'}>}[In $ Mils]/Sum({<[Line Item Grouping] = {'Net Revenue'}>}[In $ Mils])

Also, is there any way that we can talk for some time? I just want to understand whether those are possible in QLIK VIEW.